Formula too long error...

[NWO]

Hello all.

I have the following formula in a cell that works just fine. However, I
need to add one more SUMIF statement but I get an error stating that the
formula is too long. Is there an easy way around this - thank you.

NWO

=SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E11",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E12",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E13",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E16",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E17",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))

 

[Ron Coderre]

Try this:
=SUMPRODUCT((GoodBoth!$F$2:$F10000="ME")*(GoodBoth!$Q$2:$Q$10000<=20011231)*(GoodBoth!$Q$2:$Q$10000<=20011231)*(GoodBoth!$S$2:$S$10000<>"110")*(GoodBoth!$S$2:$S$10000<>"115")*(ISNUMBER(SEARCH(GoodBoth!$R$2:$R10000,"E12.E13.E16.E17")))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[NWO]

Thanks Ron - worked perfect.

NWO
------------

 

[Ron Coderre]

Thanks for the feedback...I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro