COUNT IF (AND / OR) SUM IF PROBLEM
 

I have been trying to use COUNT IF and SUM IF functions in a worksheet, and I
have been receiving the #VALUE! error.

For Example:
I am trying to have it add up the number of personnel with "ARCOM" and "1",
just adding the number of personnel with ARCOMs to gather a total. The same
thing with other types of awards - # of personnel with other type of awards.
Just adding ARCOM and "1" (in a separate column" should work.

The formula(s) that I am using a
=COUNT(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))
and
=SUM(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))

The COUNT IF formula worked ONCE, and then I started getting the #VALUE!
error, with no resolution in sight.

This formula is on a very high priority document for us, so if you know the
answer, please post it here, or email it to me at

Thanks!!

COUNT IF (AND / OR) SUM IF PROBLEM
 

Try this:

=SUMPRODUCT(--(E7:E350="ARCOM"),--(T7:T350=1)))

"Anthony Mann" wrote:

I have been trying to use COUNT IF and SUM IF functions in a worksheet, and I
have been receiving the #VALUE! error.

For Example:
I am trying to have it add up the number of personnel with "ARCOM" and "1",
just adding the number of personnel with ARCOMs to gather a total. The same
thing with other types of awards - # of personnel with other type of awards.
Just adding ARCOM and "1" (in a separate column" should work.

The formula(s) that I am using a
=COUNT(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))
and
=SUM(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))

The COUNT IF formula worked ONCE, and then I started getting the #VALUE!
error, with no resolution in sight.

This formula is on a very high priority document for us, so if you know the
answer, please post it here, or email it to me at

Thanks!!

COUNT IF (AND / OR) SUM IF PROBLEM
 

Try this:

=SUMPRODUCT((E7:E350="ARCOM")*(--T7:T350=1))

That formula works whether the Col_T values are text or numeric.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Anthony Mann" wrote:

I have been trying to use COUNT IF and SUM IF functions in a worksheet, and I
have been receiving the #VALUE! error.

For Example:
I am trying to have it add up the number of personnel with "ARCOM" and "1",
just adding the number of personnel with ARCOMs to gather a total. The same
thing with other types of awards - # of personnel with other type of awards.
Just adding ARCOM and "1" (in a separate column" should work.

The formula(s) that I am using a
=COUNT(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))
and
=SUM(IF((E7:E350="ARCOM)+(T7:T350="1")1,0))

The COUNT IF formula worked ONCE, and then I started getting the #VALUE!
error, with no resolution in sight.

This formula is on a very high priority document for us, so if you know the
answer, please post it here, or email it to me at

Thanks!!