=COUNTA() with multiple look-up

[Robert]

Hi All,

I would like to count the number of items that have a value "AAA" in
column A and an amount >0 in column B.

As I am not quite sure how to incorporate two look-ups in a counta
formula I was wondering if anyone can give me the formula?

Many thanks!

Rgds,
Robert

 

[Guest]

Try something like this:

=SUMPRODUCT((A1:A100="AAA")*(B1:B100>0))

or....if there may be some text cells in B1:B100 interspersed with the numbers
=SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Robert]

Hi Ron,

Thanks for the suggestion!

When I fill out the formula I get #NUM! as a result.

I adjusted the formula as follows: =SUMPRODUCT((Flash_data!
A:A="PHILIPS FFC")*(Flash_data!D>0))

Am I forgetting something?

Thanks again

Rgds,
Robert

 

[Bernard Liengme]

SUMPRODUCT does not work with entire column (D)
Try something like D16500
best wishes

 

[Guest]

SUMPRODUCT cannot reference entire columns; it must refer to ranges.

Try this:
=SUMPRODUCT((Flash_data!A1:A65535="PHILIPS FFC")*(Flash_data!D165535>0))

NOTE: the formula refers to the next-to-the-last row....NOT the last row
(65536)

Alternatively, you could skip the first row, instead:
=SUMPRODUCT((Flash_data!A2:A65536="PHILIPS FFC")*(Flash_data!D265536>0))

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

XL2002, WinXP

 

[Robert]

Hi Ron and Bernard,

Thanks, that does the trick!

Rgds,
Robert