Countif with AND logic

[Guest]

I have a worksheet where I'm try to count the number of items with JobCode=P
and an amount greater than 0. I think I should be able to do it with a
COUNTIF and AND logic but I keep getting errors. Anyone help me with this?
Thanks. Michael

JobCode Amount
P 20
C 10
P 0
P 0
P 10

 

[JulieD]

Hi

you need to use the SUMPRODUCT function
=SUMPRODUCT(--(A1:A10="P"),--(B1:B10>0))

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for details on how this works

cheers
JulieD

 

[CLR]

Another way is to CONCATENATE the two columns into a third column and then
do a COUNTIF on that column, (P20).........you can also so this for more
than two columns.

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Difficult with greater than 0 Chuck!

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[CLR]

Maybe, but this seems to work with the OP's sample data...........
=A1&"_"&B1 in C1 and copy down..............

=COUNTIF(C:C,">P_0") in cell D1

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Yeah but a value of Q in A and 0 in B also counts in this method, which
doesn't meet the OP's criteria.

Bob

 

[CLR]

True, but then how about.....

=COUNTIF(C:C,">P_0")-COUNTIF(C:C,">=Q_0")

it seems to fit, but then like you said, "it's getting difficult" <g>

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Q was just an example, it is true of R, S, T, etc.

It works in a too restrictive set.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[CLR]

Maybe so, but the same modified formula seems to take care of the R,S,T, etc
conditions as well in my model.........

=COUNTIF(C:C,">P_0")-COUNTIF(C:C,">=Q_0")

Vaya con Dios,
Chuck, CABGx3