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<=2001123 1,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(Good Both!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$1 0000<"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<=200 11231,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(Good Both!$R$2:$R10000="E17",IF(GoodBoth!$Q$2:$Q$10000< =20011231,IF(GoodBoth!$S$2:$S$10000<"110",IF(Good Both!$S$2:$S$10000<"115",1,0))))))

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")*(GoodBo th!$S$2:$S$10000<"115")*(ISNUMBER(SEARCH(GoodBoth !$R$2:$R10000,"E12.E13.E16.E17")))


Does that help?

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

XL2002, WinXP-Pro


"NWO" wrote:

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<=2001123 1,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(Good Both!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$1 0000<"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<=200 11231,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(Good Both!$R$2:$R10000="E17",IF(GoodBoth!$Q$2:$Q$10000< =20011231,IF(GoodBoth!$S$2:$S$10000<"110",IF(Good Both!$S$2:$S$10000<"115",1,0))))))

Thanks Ron - worked perfect.

NWO :)
------------

"Ron Coderre" wrote:

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")*(GoodBo th!$S$2:$S$10000<"115")*(ISNUMBER(SEARCH(GoodBoth !$R$2:$R10000,"E12.E13.E16.E17")))


Does that help?

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

XL2002, WinXP-Pro


"NWO" wrote:

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<=2001123 1,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(Good Both!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$1 0000<"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<=200 11231,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(Good Both!$R$2:$R10000="E17",IF(GoodBoth!$Q$2:$Q$10000< =20011231,IF(GoodBoth!$S$2:$S$10000<"110",IF(Good Both!$S$2:$S$10000<"115",1,0))))))

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

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

XL2002, WinXP-Pro


"NWO" wrote:

Thanks Ron - worked perfect.

NWO :)
------------

"Ron Coderre" wrote:

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")*(GoodBo th!$S$2:$S$10000<"115")*(ISNUMBER(SEARCH(GoodBoth !$R$2:$R10000,"E12.E13.E16.E17")))


Does that help?

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

XL2002, WinXP-Pro


"NWO" wrote:

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<=2001123 1,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(Good Both!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$1 0000<"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<=200 11231,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(Good Both!$R$2:$R10000="E17",IF(GoodBoth!$Q$2:$Q$10000< =20011231,IF(GoodBoth!$S$2:$S$10000<"110",IF(Good Both!$S$2:$S$10000<"115",1,0))))))