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

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

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