Use SUMPRODUCT to count cells which CONTAINS certain text/numbers

[booYEAH]

I am attempting to sum the number of occurrences for the following:
(1) Column C contains the number 3 (all cells in this column contain both text and numbers)
(2) Column B equals "Open"
(3) Column K equals "Design"

This is my working formula, but isn't working. The error is in the first array, I've forgotten how to find/sum cells which contain certain text/numbers.

=SUMPRODUCT(
--ISNUMBER(MATCH('AIM data'!$C$2:$C$10000,{"*3*"},0)),
--('AIM data'!$B$2:$B$10000="Open"),
--('AIM data'!$K$2:$K$10000="Design"))

TIA -James

 

[Brian from Maui]

Use SEARCH, instead of MATCH

=SUMPRODUCT(--(ISNUMBER(SEARCH(3,'AIM data'!$C$2:$C$10000)),
--('AIM data'!$B$2:$B$10000="Open"),
--('AIM data'!$K$2:$K$10000="Design"))

 

[booYEAH]

Thanks for the quick response, Brian. Excel doesn't like that setup, it won't allow me to enter that configuration? That's odd.

 

[pgc01]

Hi

There's just one parenthesis missing in the first condition:

=SUMPRODUCT(--(ISNUMBER(SEARCH(3,'AIM data'!$C$2:$C$10000))),...