CODEWITHSUNDEEP
arraysgoogle-sheetssumspreadsheetcountif
Lily
Lily

Reputation: 1

Count if using multiple criteria in Google Sheets

I am trying to to count the number of cells having values either 1 or mp,1 or 1,mp through B3 to AC3.

Here's what I have tried so far:

=SUM(IF(OR(B3:AC3=1, ISNUMBER(SEARCH("1,mp", B3:AC3)),
ISNUMBER(SEARCH("mp,1", B3:AC3))),1,0))

=SUM(IF(OR(B3:AC3=1, ISNUMBER(IFERROR(SEARCH("1,mp", B3:AC3),0))), 1,
ISNUMBER(IFERROR(SEARCH("mp,1", B3:AC3),0))))

=SUM(IF(OR(B3:AC3=1, B3:AC3="1,mp", B3:AC3="mp,1"), 1, 0))`

=SUM(IF(OR(B3:AC3=1, SEARCH("1,mp",B3:AC3), SEARCH("mp,1",B3:AC3)), 1, 0))

Unfortunately, all of them are are giving me same error: An array value could not be found

Upvotes: 0

Views: 60

Answers (1)

Martín
Martín

Reputation: 10217

You can try summing COUNTIF:

=COUNTIF(B3:AC3,1)+COUNTIF(B3:AC3,"1,mp")=COUNTIF(B3:AC3,"mp,1")

Or filtering and counting values:

=COUNTA (IFNA(FILTER(B3:AC3, REGEXMATCH(TO_TEXT(B3:AC3),"^[1|1,mp|mp,1]$"))))

Upvotes: 0

Related Questions