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

Hi

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

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

for details on how this works

cheers
JulieD


"mlkpied" wrote in message
...
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

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


"mlkpied" wrote in message
...
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

Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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

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" wrote in message
...
Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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

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



"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Difficult with greater than 0 Chuck!

--

HTH

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


"CLR" wrote in message
...
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


"mlkpied" wrote in message
...
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