Formula to long

**pdmkh** · 05-22-2011, 08:12 AM

looking for some help on an issue I am having with a prompt stating that a formula is two long. I am using SUMPRODUCT ISNUMBER SEARCH formula for multiple selections, approx 30, and the way I am entering the code works but "formula to long" error message appears before I am able to enter criteria for all 30 selections. I am just adding each of the equations to each other to return a total. attached is a portion of the formula. Is there a different way of listing the criteria that is more efficient?? Please help!!

Please Login or Register  to view this content.

**zbor** · 05-22-2011, 08:41 AM

Please open only one thread.

**daddylonglegs** · 05-22-2011, 11:09 AM

You can use a formula like this

=SUM((MMULT(ISNUMBER(SEARCH({"BM","ENG"},'T-WEEK DATA'!G6:G2283))*('T-WEEK DATA'!U6:U2283="T-9"),LEN({"BM";"ENG"})^0)>0)+0)

You can expand the {"BM","ENG"} parts as much as you like - Note that the first array constant uses comma separators, the second uses semi-colons.

MMULT is limited to 5461 rows in Excel 2003

.......or if you list all 30 criteria in a column range, e.g. Z1:Z30 on same sheet as formula then you can use this version

=SUM((MMULT(ISNUMBER(SEARCH(TRANSPOSE(Z1:Z30),'T-WEEK DATA'!G6:G2283))*('T-WEEK DATA'!U6:U2283="T-9"),LEN(Z1:Z30)^0)>0)+0)

That needs to be confirmed with CTRL+SHIFT+ENTER

Edit: I deleted your duplicate post

**pdmkh** · 05-23-2011, 08:08 AM

Thank you very much for the help. I used the formula listed below and it work perfectly. Can you add another criteria?? I have tried to add ('T-WEEK DATA'!L6:L2283="1") with no luck returning the proper number. I must be missing something when I am adding to the formula. Any help would be appreciated.
=SUM((MMULT(ISNUMBER(SEARCH(TRANSPOSE(Z1:Z30),'T-WEEK DATA'!G6:G2283))*('T-WEEK DATA'!U6:U2283="T-9"),LEN(Z1:Z30)^0)>0)+0)

**daddylonglegs** · 05-23-2011, 03:35 PM

Did you try it like this

=SUM((MMULT(ISNUMBER(SEARCH(TRANSPOSE(Z1:Z30),'T-WEEK DATA'!G6:G2283))*('T-WEEK DATA'!U6:U2283="T-9")*('T-WEEK DATA'!L6:L2283="1"),LEN(Z1:Z30)^0)>0)+0)

.....although "1" signifies a text value so normally if you have numeric data you don't need the quotes, just use 1.....

**pdmkh** · 07-22-2011, 11:55 AM

Sorry for the delay in responding. The info you supplied worked very well and returned exactly what I needed. I am a little worried about the number of rows this funtion is able to review. is ther another formula that is not as limited that could provide the same information?