countif with multiple criteria
 

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

countif with multiple criteria
 

Something like this, in say D2:
=sumproduct((a2:a1000)*(b2:b1000)*(c2:c1000))
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
---
"blswes" wrote:
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

countif with multiple criteria
 

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:A100)*(B2:B100)*(C2:C100))

Andrea Jones
www.stratatraining.co.uk

"blswes" wrote:

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

countif with multiple criteria
 

You can only use COUNTIF for a single condition. For more, you can use
SUMPRODUCT, along these lines:

=SUMPRODUCT((range_10)*(range_20)*(range_30))

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

Hope this helps.

Pete

On Jul 30, 4:13*pm, blswes wrote:
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

countif with multiple criteria
 

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

"Max" wrote:

Something like this, in say D2:
=sumproduct((a2:a1000)*(b2:b1000)*(c2:c1000))
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
---
"blswes" wrote:
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

countif with multiple criteria
 

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

"blswes" wrote:

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

"Max" wrote:

Something like this, in say D2:
=sumproduct((a2:a1000)*(b2:b1000)*(c2:c1000))
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
---
"blswes" wrote:
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