CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacountif
Meg
Meg

Reputation: 3

Count how many cells contain specific words in Google Sheets

I'm analyzing a large dataset and trying to figure out how to best count how many cells contain a particular set of words without duplicating cells where these words appear multiple times in a single cell. For example, I'd like to count the total number of cells that contain either "blue" OR "green" but only have cells labeled as "blue green" count once.

I've tried:

=countifs(A:A,"*blue*",A:A,"green")

or

=ArrayFormula(SUM(COUNTIFS(A:A,{"*blue*","*green*"})))

but these don't address what I'm trying to accomplish.

Upvotes: 0

Views: 127

Answers (1)

JPV
JPV

Reputation: 27282

Try

=SUMPRODUCT(REGEXMATCH(A:A, "(?i)blue|green"))

and see if that helps?

Upvotes: 0

Related Questions