Excel - count if multiple words are in a cell

Question

I have values

 AA,BB,CC
 AA,CC
 AA
 AA,BB
 BB
 BB,CC
 CC
 CC,AA
 CC,BB
 BB,CC,DD

To find all cells that have. how do I search for each value?

 AA,BB = 2
 BB,CC = 4

Example

COUNTIFS($A:$A,"*AA*",$A:$A,"*BB*") doesn't seem to work.

Answer 1

Try this using wildcards * with TEXTBEFORE() & TEXTAFTER()

---

• Formula used in cell D2

=COUNTIFS(A1:A10,"*"&TEXTBEFORE(C2,",")&"*",A1:A10,"*"&TEXTAFTER(C2,",")&"*")