Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
COUNTIF() with multiple criteria | Excel Discussion (Misc queries) | |||
Help please - Countif with multiple criteria | Excel Worksheet Functions | |||
Countif Multiple Criteria | Excel Discussion (Misc queries) | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |