Count Multiple items within a single cell - Countif ?

Romano el Polako

Hello Team,

I hope you are doing great!

I have a similar question. In the scenario, I am trying to resolve, I have a single cell with multiple values, where I would like to count the occurrence of specific word.

=COUNTIFS([Defect Category]@row; CONTAINS("Decision"; @cell))

Current status:

• Formula provides me with 1 as count of "Decision" in the cell

Desired outcome

• It should show actual count, even if value is duplicated. In our case column Decision Error shall be "2"

Could you please advise?

Thanks,

Roman

Genevieve P.

Hi @Romano el Polako

The formula is checking to see if the cell contains that value, and if it does (once, twice, three times) then the answer is "yes!" or 1, which is why you see 1 instead of 2. It is unable to count the cell for multiple instances of the same word.

I don't believe there's a way to do this based on your current set-up, but I'd love to be proved wrong if someone else can come up with a clever solution!

Cheers,

Genevieve

Paul Newcome

@Romano el Polako We just did this in another thread last week I believe.

Basically you remove the desired word from the string entirely and subtract that character count from the original character count. Dividing that by the number of character in a single instance of that word should give you how many times that word appears in a cell.

=(LEN([Defect Category]@row) - LEN(SUBSTITUTE([Defect Category]@row, "Decision", ""))) / LEN("Decision")

Using the circled row in your screenshot, that gives us

=(31 - 15) / 8

=(16) / 8

=2

Romano el Polako

Hey!

Thank you Paul, solution works, outstanding!

Thanks,

Roman

Paul Newcome

@Romano el Polako Happy to help. 👍️