Using COUNTIF with multiple criterium

  • Thread starter Thread starter Rich Palarea
  • Start date Start date
R

Rich Palarea

In my worksheet, I have the following columns:

CODE SERVICE
IDD COM. GROUND
MAN COM. GROUND
IST COM. GROUND
MAN COM. GROUND

I would like to use COUNTIF or similar counting function to total the number
of records that match MAN and COM. GROUND.

I've tried nested IF/AND statements, but I can't seem to run a function
inside of the IF statement when value is true.

Any help is appreciated!

Thanks,
Rich
 
With your data (including headers) in A1:B5
=SUMPRODUCT(((A2:A5)="MAN")*((B2:B5)="COM. GROUND"))

OR

=SUMPRODUCT(((A2:A5)=C1)*((B2:B5)=C2))
if your search parameters change

Dan E
 
Dan:

Doesn't SUMPRODUCT only work on numbers? I'm counting the number of records
that have a text label in them (in both cases).

I get a #NUM error if I use your solution?

Am I doing something wrong?

Thanks,
Rich
 
Rich,

You make them numbers by entering conditions.
A2 = "MAN" returns FALSE (ie 0)
A3 = "MAN" returns TRUE (ie 1)
A4 = "MAN" returns FALSE (ie 0)
A5 = "MAN" returns TRUE (ie 1)
B2 = "COM. GROUND" returns TRUE (1)
B3 = "COM. GROUND" returns TRUE (1)
B4 = "COM. GROUND" returns TRUE (1)
B5 = "COM. GROUND" returns TRUE (1)

SUM PRODUCT = A2*B2 + A3*B3 + A4*B4 + A5*B5
= 0*1 + 1*1 + 0*1 + 1*1
= 2

I couldn't tell you why your getting a #NUM error. It works
for me???

Dan E
 
Just one more thing - not trying to be ungrateful !

With those records filered, is there a way I can now total a third column of
"charges" that match those filtered records?

Looking for the sum of "charges", where ColA is "MAN" and ColB is "COM.
GROUND". Let's say ColC is the charge in dollar amount format.

Thanks!
Rich
 
I'm not sure I follow you completely, but i'll give it a shot
CODE SERVICE PRICE
IDD COM. GROUND 1000
MAN COM. GROUND 400
IST COM. GROUND 3420
MAN COM. GROUND 800

You want the sum of column C when column A is MAN and
column B is COM. GROUND

=SUMPRODUCT(((A2:A5)="MAN")*((B2:B5)="COM. GROUND"),(C2:C5))

watch out for text wrapping

Dan E
 
Back
Top