Formula too long

Hi all,

Is there any way to make the forumla below shorter? Not only does it take a long time to calculate several variations at once, but some variations are too long to fit in.

=SUMPRODUCT((LEFT(January!$K$2:$K$10000,2)="US")*(LEFT(January!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",January!$E$2:$E$10000))))
+
SUMPRODUCT((LEFT(February!$K$2:$K$10000,2)="US")*(LEFT(February!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",February!$E$2:$E$10000))))
+
SUMPRODUCT((LEFT(March!$K$2:$K$10000,2)="US")*(LEFT(March!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",March!$E$2:$E$10000))))
+
SUMPRODUCT((LEFT(April!$K$2:$K$10000,2)="US")*(LEFT(April!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",April!$E$2:$E$10000))))
+
SUMPRODUCT((LEFT(May!$K$2:$K$10000,2)="US")*(LEFT(May!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",May!$E$2:$E$10000))))
+
SUMPRODUCT((LEFT(June!$K$2:$K$10000,2)="US")*(LEFT(June!$M$2:$M$10000,LEN($C$3))=$C$3),--(ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",June!$E$2:$E$10000))))

Up until now I've been splitting it up into two cells, but it still takes a very long time to calculate it all.

This will be even more problematic should I need to add more months.

Thanks!

Originally Posted by martindwilson

it might be better to add a helper column on each sheet =and(left(k2,2)="us",LEFT(M2,LEN($C$3))=$C$3,ISNUMBER(SEARCH("MAT - INSUFFICIENT CP STOCK",$E$2) then countif(that column,true)

I'll give it a go, unfortunately I'll have a lot of new columns for all the variations.

Originally Posted by Ace_XL

Do you really need to scan through 10000 rows for each SUMPRODUCT? Keep the range to the number of rows with data. It will save calculation time

Yes, I have close to 10000 rows on every sheet