Mulitple COUNTIFs ?

  • Thread starter Thread starter JRM
  • Start date Start date
J

JRM

How can I use the COUNTIF function to 'count if' a cell contain
multiple values.
ie
I want to count a range of cells if the value of any cell is either "A
or "F" or "X" but ignore any other values.
I would prefer to do this in one statement - is it possible & how.
Thanks
Joh
 
By the way, you can also use "multiple countifs", just add them together
like below, but generally, the fewer functions are used for the same job,
the happier everyone is.

=countif(a1:a8,"a")+countif(a1:a8,"f")+countif(a1:a8,"x")
 
=SUMPRODUCT(--ISNUMBER(MATCH(Range,{"A","F","X"},0)))

or with conditions in some range, say Z2:Z4,...

=SUMPRODUCT(--ISNUMBER(MATCH(Range,$Z$2:$Z$4,0)))

Note that the conditions are in ascending order, although MATCH with
match-type set to 0 does not require such order. It makes the formula a bit
faster.
 
Thanks Dave
Used the Countif + countif etc - it worked brilliant for what I need a
the moment but will check out the other when time permits.
Thanks again
Joh
 
Back
Top