Counting based on criteria from two cells???

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Use SUMPRODUCT. You must specify a range.


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

HTH,
Paul
 
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)
 
Thank you, this did help.

Attila

Ron Coderre said:
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)
Click to expand...
 
Back
Top