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

Microsoft Excel

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionCarenC

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?

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

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

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.

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

=SUBTOTAL(103,Table2[Statu