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
Microsoft Excel

Last Comment
CarenC
Steve

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If you just want to count the number of visible rows after filtering, use subtotal with code 103 instead of 3. Code 103 ignores hidden values.

=SUBTOTAL(103,Table2[Status])
CarenC

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(D8:D204)-ROW(D8),0))
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?
Steve

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="Established"),SUBTOTAL(3,OFFSET(D8,ROW(D8:D204)-ROW(D8),0)),N(E8:E204>=150),N(F8:F204>0.5))

More to follow...
Steve

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.
CarenC

Excellent and very clear explanation.  Thank you!!
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY