Excel 2007 : Excel Count If with mulitple criteria in one column and count in another column

[1609emily]

I need a formula to count if in column : g1:g500 the value 110F,107,131F are there and if in column d1:D500 a "P" is there. Column G has other numerical values as well as text values.

Column D Column G
P 110F
D 107
R 131F
P 202
C 300
P 131F

So i need the formula to tell me that there are 2 groups of cells that match this (the first one and last one in this case).

[TMS]

The lazy way ...

=COUNTIFS(D:D,"P",G:G,"110F")+COUNTIFS(D:D,"P",G:G,"107")+COUNTIFS(D:D,"P",G:G,"131F")


Regards, TMS

[1609emily]

Is there any way to do this without typing in countifs for each value (110f, 107, etc). I actaully have about 100 values that I have to type in and was hoping to find a shorter way? I know I have to type in those 100 values but do I have to use the countifs formula for each one or is there a way to do countifs( 110f,107,131f,300,400,500,600,700,etc).?

[TMS]

This is the problem when you simplify your example ... you get a simple solution. I did say it was the lazy way.

If you were to list the codes you are looking for in, say L1 to L500, you could use:

=SUMPRODUCT(--(D1:D500="P"),--(COUNTIF(L1:L500,G1:G500)>0))

Worked OK with your sample data ...


Regards, TMS