countif with a twist

  • Thread starter Thread starter Ed
  • Start date Start date
E

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.
 
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
 
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



.
Click to expand...
 
Back
Top