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
CarenCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Does this about do it...

=SUMPRODUCT((D8:D204="Established")+0,SUBTOTAL(3,OFFSET(D8,ROW(D8:D204)-ROW(D8),0)),N(E8:E204>=150),N(F8:F204>0.5))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ejgil HedegaardCommented:
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])
0
CarenCAuthor Commented:
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?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

SteveCommented:
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...
0
SteveCommented:
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.
0
CarenCAuthor Commented:
Excellent and very clear explanation.  Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.