Sumproduct multiple ISNUMBER

[sirdon]

I have the current formula to add up certain text values in a cell. However it is not reconsigning multiple criteria's.

How do i add multiple ISNUMBER criteria's??

Please Login or Register  to view this content.

[hrlngrv]

Are you really serious that you need a 551-column range? A:UE spans 551 columns. The QUERY calls are only accessing Col2, Col7 and Col12, so it seems you only need A:L.

Anyway, you want to count the number of records in the IMPORTRANGE table for which Col2 equals the value of cell A2, Col7 contains the string "GPS Round" and Col12 contains any of the substrings "Washed out (no play commenced)", "Bye", "Match Abandoned (no play commenced)", or "Extreme Heat (no play commenced)". If so, I believe

Please Login or Register  to view this content.

would be the better way to go about this. That is, if you're using QUERY anyway, make it do as much work as possible. That is, make it handle the Col7 and Col12 comparisons. In this case, multiple QUERY calls applied to the same IMPORTRANGE table are unnecessary and should be replaced by a single QUERY call.

The QUERY call returns 2 rows in 1 column, the label "count" in the top row and a numeric count in the 2nd row. The SUM call reduces this to just the count. INDEX(QUERY(...),2,1) would produce the same result, but why type so much more text?

Note: this site is acting up about SQL injection attacks, so I had to put "select " in a separate string. You could combine it with "count...".

[sirdon]

hrlngrv, thank you so much. I was well off the pace with my formula. What you have provided works great. Thank you so much. ANd yes you are right, i can easily change it to A:L, rather than A:UE so i have done that as well.