asked on 9/6/2013

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

9/6/2013

ASKER CERTIFIED SOLUTION

Steve

9/6/2013

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.

See Pricing Options

Start Free Trial

Ejgil Hedegaard

9/6/2013

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

9/6/2013

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(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

9/6/2013

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

9/6/2013

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

9/6/2013

ASKER

Excellent and very clear explanation. Thank you!!