Countif

[rbulph]

How can I determine how many cells in a range contain at least one of
range of possible values? For example suppose each cell contains a lis
of colours such as "Red, Blue, Yellow". I can use COUNTIF with "*Red*
as the second argument to count the number of cells that contain "Red"
But if I want to count the number of cells that contain either or bot
of "Red" and "Blue" it seems much harder. How do I do this

 

[RagDyer]

One way:

=COUNTIF(A1:A10,"*red*")+COUNTIF(A1:A10,"*blue*")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


How can I determine how many cells in a range contain at least one of a
range of possible values? For example suppose each cell contains a list
of colours such as "Red, Blue, Yellow". I can use COUNTIF with "*Red*"
as the second argument to count the number of cells that contain "Red".
But if I want to count the number of cells that contain either or both
of "Red" and "Blue" it seems much harder. How do I do this?

 

[Alan Beban]

If only one cell contains red blue (and the other 9 contain, e.g.,
green), this formula returns 2.

Alan Beban

 

[rbulph]

Maybe it's best to write my own function. It's not very hard to do if
you just loop through all the cells and use Instr on each string you're
searching for. My only worry is that it has to search for up to 8
strings over 2000 cells, so it could be a bit slow, and will redo the
calculation whenever I make a change to one of those cells.