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.

Who is Participating?
SteveConnect With a Mentor Commented:
Does this about do it...


Open in new window

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.

CarenCAuthor Commented:

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])

Makes sense buy why the "+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)
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?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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:


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.
CarenCAuthor Commented:
Excellent and very clear explanation.  Thank you!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.