countif with multiple criteria

[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

 

[Max]

Something like this, in say D2:
=sumproduct((a2:a100>0)*(b2:b100>0)*(c2:c100>0))
Adapt the ranges to suit. All ranges must be identical size.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[Andrea Jones]

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

 

[Pete_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

 

[blswes]

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

 

[Andrea Jones]

Just add another *(D210) 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