Avatar of CarenC
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
Microsoft Excel

Avatar of undefined
Last Comment
CarenC

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve

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
Sign up - Free for 7 days
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.
See how we're fighting big data
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 question
Ejgil Hedegaard

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])
ASKER
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...
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.
ASKER
CarenC

Excellent and very clear explanation.  Thank you!!