Using COUNTIF with multiple criterium

[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

 

[Dan E]

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

 

[Rich Palarea]

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

 

[Dan E]

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

 

[Rich Palarea]

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

 

[Dan E]

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