countif with multiple criteria

  • Thread starter Thread starter blswes
  • Start date Start date
B

blswes

How do I count entries that meet multiple criteria across multiple columns of
data?

Specifically, I want to count how many entries have a value greater than 0
in all three different data columns that I'm ranging.

I feel like it's some combination of COUNTIF and AND that's beyond me.

Thanks,
Ben
 
Are you wanting to count how many rows there are where all 3 numbers on the
row are greater than 0? If that's the case you can use a SUMPRODUCT function
like

=SUMPRODUCT((A2:A10>0)*(B2:B10>0)*(C2:C10>0))

Andrea Jones
www.stratatraining.co.uk
 
You can only use COUNTIF for a single condition. For more, you can use
SUMPRODUCT, along these lines:

=SUMPRODUCT((range_1>0)*(range_2>0)*(range_3>0))

Substitute your ranges, but you can't use full-column references with
SP (unless you have Excel 2007).

Hope this helps.

Pete
 
Great. Thank you for the SUMPRODUCT solution.

As a follow-up question, if there are 25 entries that have values greater
than 0 across my three different columns (A,B,C), how would I add up the
values from an additional column (D) for only the entries that met my
original criteria?

Thanks,
Ben
 
Just add another *(D2:D10) at the end of the SUMPRODUCT function. If the
other columns do have values >0 then that part of the function returns 1*1*1
(ie all true), by multiplying the result of this by the fourth column the
function will then add all the resulting values together (for the count
example you are actually summing the 1s that result from the 1*1*1 for each
row).

Andrea Jones
www.stratatraining.co.uk
 
Back
Top