Whats wrong with this formula?

Originally Posted by Shane Moore

Hi,

This formula works up to the COUNTIF(D10:D10,"na") item but does not work after I put in the final COUNTIF(D18:D18,"na") ???

If there a limitation on the number of SUMs and/or COUNTIF's that I can have?

Thanks,

Shane.

=SUM(COUNTIF(D2:D2,"y")+SUM(COUNTIF(D5:D5,"y")+SUM(COUNTIF(D10:D10,"y")+SUM(COUNTIF(D18:D18,"y")+SUM(COUNTIF(D2:D2,"na")+SUM(COUNTIF(D5:D5,"na")+SUM(COUNTIF(D10:D10,"na")+SUM(COUNTIF(D18:D18,"na")))))))))

Hi,

did you mean to do

=SUM(COUNTIF(D2:D2,"y"))+SUM(COUNTIF(D5:D5,"y"))+SUM(COUNTIF(D10:D10,"y"))+SUM(COUNTIF(D18:D18,"y"))+SUM(COUNTIF(D2:D2,"na"))+SUM(COUNTIF(D5:D5,"na"))+SUM(COUNTIF(D10:D10,"na"))+SUM(COUNTIF(D18:D18,"na"))

or Sum as you wrote, the original breaks the '7 levels of 'IF' rule

hth
---

same answer as

=IF(D2="y",1,0)+IF(D5="y",1,0)+IF(D10="y",1,0)+IF(D18="y",1,0)+IF(D2="na",1,0)+IF(D5="na",1,0)+IF(D10="na",1,0)

---
same answer as

=IF(OR(D2="y",D2="na"),1,0)+IF(OR(D5="y",D5="na"),1,0)+IF(OR(D10="y",D10="na"),1,0)+IF(D18="y",1,0)

---