countif with a twist

[Ed]

The twist is that that I need the criteria in my countif
function to be based on two things and on of those things
is based on another range. For instance, the function
I'm looking for would tell me how many cells in a range
are greater than zero AND have an adjacent cell (same
row, different column) containing a value that is equal
to a particular string, say "AA".

All help is greatly appreciated. Thanks.

 

[Frank Kabel]

Hi Ed
COUNTIF accepts only one condition. You can use SUMPRODUCT. e.g.,
=SUMPRODUCT((A1:A999>0)*(B1:B999="AA"))
this will count alls cells which are greater than zero and have the
string "AA" in the adjacent column

To sum the cells in column A which met the criteria use
=SUMPRODUCT((A1:A999>0)*(B1:B999="AA")*(A1:A999))

HTH
Frank

 

[Paul Moles]

Try http://www.cpearson.com/excel/lookups.htm

Cheers

Paul

 

[Guest]

Thanks - this does the job!

-----Original Message-----
Hi Ed
COUNTIF accepts only one condition. You can use SUMPRODUCT. e.g.,
=SUMPRODUCT((A1:A999>0)*(B1:B999="AA"))
this will count alls cells which are greater than zero and have the
string "AA" in the adjacent column

To sum the cells in column A which met the criteria use
=SUMPRODUCT((A1:A999>0)*(B1:B999="AA")*(A1:A999))

HTH
Frank



.