Countif to count more than one item?

A

Adam

All,

Can Countif function count more than one criteria? I.e I have a column and want to count all but one of the criteria in that column.

Column has MD, LK, Fac and No in it, I want to count all but No.

Is this possible with the countif function?
 
J

Jan Karel Pieterse

Hi Adam,
Can Countif function count more than one criteria?
I.e I have a column and
want to count all but one of the criteria in that column.

Column has MD, LK, Fac and No in it, I want to count all but No.
Click to expand...

Lets say this is column A, then try this array formula.

=SUM(IF(OR(A1:A100="MD",A1:A100="LK",A1:A100="Fac"),1,0))

Enter the formula by hitting control-shift-enter. Excel will add {} around the formula.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
L

Leo Heuser

Adam

Try:

=COUNTIF(A2:A1000,"<>No")

To count all Fac's and No's, try

=SUMPRODUCT((A2:A1000="Fac")*(A2:A1000="No"))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Adam said:
All,

Can Countif function count more than one criteria? I.e I have a column and
Click to expand...
want to count all but one of the criteria in that column.
 
A

Andy B

Hi

Try
=COUNTIF(A2:A1000,"<>No")

--
Andy.


Adam said:
All,

Can Countif function count more than one criteria? I.e I have a column and
Click to expand...
want to count all but one of the criteria in that column.
 
Top