Need help abbreviating an IF(COUNTIFS formula

  • Thread starter Thread starter lynxwomancat
  • Start date Start date
L

lynxwomancat

I have a formula that when I hit enter says: "The specified formula
cannot be entered because it uses more levels of nesting than are
allowed in the current file format."

Here is my formula:
=IF(COUNTIFS($DP2,"A VIP",$DQ2,"B Customer"),"B
Customer",IF(COUNTIFS($DQ2,"",$DR2,"B Customer"),"B
Customer",IF(COUNTIFS($DP2,"A VIP",$DQ2,""),"A
VIP",IF(COUNTIFS($DQ2,"B Customer",$DR2,"C Prospect"),"B
Customer",IF(COUNTIFS($DQ2,"",$DR2,"C Prospect"),"C
Prospect",IF(COUNTIFS($DQ2,"B Customer",$DR2,""),"B
Customer"),IF(Countifs($DQ2,"B Customer",$DS2,"D Lead Source"),"B
Customer",IF(COUNTIFS($DQ2,"B Customer",$DS2,""),"B
Customer",IF(Countifs($DQ2,"",$DS2,"D Lead Source"),"D Lead
Source")))))))

There are a few more variations I'd like to add to this formula but
I'm already exceeding limits. Is there a solution to achieve this same
outcome that will not exceed nesting levels?

Thank you!
Sandy
 
=IF(OR(COUNTIFS($DP2,"A VIP",$DQ2,"B Customer"),COUNTIFS($DQ2,"",$DR2,"B
Customer"),COUNTIFS($DQ2,"B Customer",$DR2,"C Prospect"),
COUNTIFS($DQ2,"B Customer",$DR2,""),COUNTIFS($DQ2,"B
Customer",$DS2,"D Lead Source"),COUNTIFS($DQ2,"B Customer",$DS2,"")),"B
Customer",
IF(COUNTIFS($DP2,"A VIP",$DQ2,""),"A VIP",
IF(COUNTIFS($DQ2,"",$DR2,"C Prospect"),"C Prospect",
IF(COUNTIFS($DQ2,"",$DS2,"D Lead Source"),"D Lead Source",""))))
 
Beautiful!!!! Thank you so very much! Just when I think I'm 'getting
it' I run into another opportunity to learn...
 
Back
Top