=COUNTA() with multiple look-up

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert - 05 Apr 2007 13:58 GMT

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

Reply to this Message

Ron Coderre - 05 Apr 2007 14:08 GMT

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

> Hi All,
>
[quoted text clipped - 8 lines]
> Rgds,
> Robert

Reply to this Message

Robert - 05 Apr 2007 14:27 GMT

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:D>0))

Am I forgetting something?

Thanks again

Rgds,
Robert

Reply to this Message

Bernard Liengme - 05 Apr 2007 14:39 GMT

SUMPRODUCT does not work with entire column (D:D)
Try something like D1:D6500
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hi Ron,
>
[quoted text clipped - 11 lines]
> Rgds,
> Robert

Reply to this Message

Ron Coderre - 05 Apr 2007 14:46 GMT

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

Try this:
=SUMPRODUCT((Flash_data!A1:A65535="PHILIPS FFC")*(Flash_data!D1:D65535>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!D2:D65536>0))

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

XL2002, WinXP

> Hi Ron,
>
[quoted text clipped - 11 lines]
> Rgds,
> Robert

Reply to this Message

Robert - 10 Apr 2007 08:12 GMT

Hi Ron and Bernard,

Thanks, that does the trick!

Rgds,
Robert

Reply to this Message