Count cells that contain both of these two words

[champchamp]

I have a column like this

Red Ferrari Blue Red Ferrari
Blue Ferrari Yellow
Yellow Ferrarired
Red Lambo Red

I want to scan through the whole column and count the cells that contain BOTH words "ferrari" and "red" regardless of where they are.
So in this case the count should be 2 (first and third rows are the only ones containing both words regardless of how many times these words occur).

What i tried so far was :
=SUM(COUNTIF(A:A,{"red","ferrari"}))

However this gives a sum of all occurances and not the number of cells.

 

[Jon von der Heyden]

Perhaps:

=SUM(COUNTIF(A:A,{"*red*ferrari*","*ferrari*red*"}))

 

[champchamp]

Thanks. I found a better solution:

=COUNTIFS(A:A,"*red*",A:A,"*ferrari*")

My problem now is that I want to replace red with a cell number. If I just replace red with B1 it doesn't work with the *.

So I need this but it doesn't work:
=COUNTIFS(A:A,"*B1*",A:A,"*B2*")



if I use this it doesn't work the way I want it:
=COUNTIFS(A:A,B1,A:A,B2)

Any ideas?

 

[Peter_SSs]

=COUNTIFS(A:A,"*"&B1&"*",A:A,"*"&B2&"*")

But just as a word of warning, it would count text such as "credit nefferrarian"
That is, it does not count words as per your description..

count the cells that contain BOTH words "ferrari" and "red"

 

[etaf]

ignore - Peter_SSs - replied as i posted

 

[champchamp]

=COUNTIFS(A:A,"*"&B1&"*",A:A,"*"&B2&"*")

But just as a word of warning, it would count text such as "credit nefferrarian"
That is, it does not count words as per your description..

This is perfect. Thanks so much.
Thanks for the warning. I will keep that in mind.

You guys are awesome.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.