Counting based on criteria from two cells???

[Guest]

I want to count in the following table the number of records where ColA>0 and
ColB=R:

ColA ColB
1000 R
1200 R
1300 R
0 R
11150 ADJ

I tried using SUMPRODUCT(--(colA_NAME>0),--(colB_NAME="R"). I defined ColA
and ColB as named ranges.

This does not seem to work and from researching on the net it seems that it
should. Any suggestions??????

Thanks in advance,

Attila

 

[PCLIVE]

Use SUMPRODUCT. You must specify a range.


=SUMPRODUCT(--(A1:A100>0),--(B1:B100="R"))

HTH,
Paul

 

[Ron Coderre]

If the named ranges refer to entire columns....SUMPRODUCT won't work.
You'd need to specify a range.

Example:
=SUMPRODUCT(A:A) returns an error

but this one returns a correct value:
=SUMPRODUCT(A1:A1000)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Thank you, this did help.

Attila

If the named ranges refer to entire columns....SUMPRODUCT won't work.
You'd need to specify a range.

Example:
=SUMPRODUCT(A:A) returns an error

but this one returns a correct value:
=SUMPRODUCT(A1:A1000)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)