CarenC
asked on
COUNTIFS with Filters (Excel 2007)
First, let me say that I've read several threads on here regarding this issue but I haven't been able to make it work. So I've attached a sample spreadsheet.
I filter on the region and count the "Building" column based on 3 criteria:
Status = "Established"
Total >= 150
Value >0.5
Cell I5 has the COUNTIFS formula.
Cell H5 uses SUMPRODUCT and SUBTOTAL and works with filtering but I can only get it to work with 1 criteria. I don't really understand how it work so that makes it really difficult to modify for 3 criteria.
--Caren
SUBTOTAL-example.xlsx
I filter on the region and count the "Building" column based on 3 criteria:
Status = "Established"
Total >= 150
Value >0.5
Cell I5 has the COUNTIFS formula.
Cell H5 uses SUMPRODUCT and SUBTOTAL and works with filtering but I can only get it to work with 1 criteria. I don't really understand how it work so that makes it really difficult to modify for 3 criteria.
--Caren
SUBTOTAL-example.xlsx
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
The_Barman,
Works perfectly even when I change D8:D204 to Table2[fieldname]. However, before I accept the solution I would like to understand the formula better so I can apply it to other situations. So here's what I understand and don't understand.
I get that the basic formula is SUMPRODUCT(array1, [array2], [array3], [array4])
array1
(D8:D204="Established")+0
Makes sense buy why the "+0"?
array2
SUBTOTAL(3,OFFSET(D8,ROW(D
I understand that SUBTOTAL(3... is a count and I understand the format of OFFSET(rows, columns, [height], [width])
but I don't understand how that all works together. I've never used height and width in OFFSET.
array3 (and array4)
N(E8:E204>=150)
Why the N(value) function? I read about the function and that "value is a value to convert to a number" but what is it looking at that isn't a number already?
Works perfectly even when I change D8:D204 to Table2[fieldname]. However, before I accept the solution I would like to understand the formula better so I can apply it to other situations. So here's what I understand and don't understand.
I get that the basic formula is SUMPRODUCT(array1, [array2], [array3], [array4])
array1
(D8:D204="Established")+0
Makes sense buy why the "+0"?
array2
SUBTOTAL(3,OFFSET(D8,ROW(D
I understand that SUBTOTAL(3... is a count and I understand the format of OFFSET(rows, columns, [height], [width])
but I don't understand how that all works together. I've never used height and width in OFFSET.
array3 (and array4)
N(E8:E204>=150)
Why the N(value) function? I read about the function and that "value is a value to convert to a number" but what is it looking at that isn't a number already?
OK:
Array logic is at play here:
So will look at rows across individually and return true / False for the criteria
The +0 does exactly the same as N() = convert true / false to 1/0
So for the formula I would use:
=SUMPRODUCT(N(D8:D204="Est
More to follow...
Array logic is at play here:
So will look at rows across individually and return true / False for the criteria
The +0 does exactly the same as N() = convert true / false to 1/0
So for the formula I would use:
=SUMPRODUCT(N(D8:D204="Est
More to follow...
So you are looking at a row of 4 criteria
if all the (n) are true you will get
1 , Subtotal , 1 , 1 : Sumproduct multiplies them together
1 x subtotal x 1 x 1 = subtotal
if any are false you will get a zero:
1 x subtotal x 0 x 1 = 0
So the N means that only rows with a true get the subtotal.
The subtotal is doing the filtering.
if all the (n) are true you will get
1 , Subtotal , 1 , 1 : Sumproduct multiplies them together
1 x subtotal x 1 x 1 = subtotal
if any are false you will get a zero:
1 x subtotal x 0 x 1 = 0
So the N means that only rows with a true get the subtotal.
The subtotal is doing the filtering.
ASKER
Excellent and very clear explanation. Thank you!!
=SUBTOTAL(103,Table2[Statu