Formula to long

[pdmkh]

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!!

=SUMPRODUCT(--ISNUMBER(SEARCH("ENG",'T-WEEK DATA'!G6:G2283))
, --('T-WEEK DATA'!U6:U2283="T-9"))
+SUMPRODUCT(--ISNUMBER(SEARCH("BM",'T-WEEK DATA'!G6:G2283)), --('T-WEEK DATA'!U6:U2283="T-9"))

[zbor]

Please open only one thread.

[daddylonglegs]

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]

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]

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]

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?