SUMPRODUCT that only includes visible rows

The other day a reader contacted me through my MeeboMe chat window. He (or she...I'll use "he" throughout for simplicity) wanted help transforming a SUMPRODUCT formula that he currently uses to be useful in situations where the data is filtered or for some other reason certain rows are hidden. Basically he wants the formula only to work on rows that are visible. Now, this reader reached out a couple of times, but I was never able to get him a solution (I had some clarifying questions.) before he went silent on me. I thought providing a solution to all of my readers on this issue might be warranted, though. This is a fairly common issue Excel users might face in real life.

The answer, basically, is the SUBTOTAL function coupled with the OFFSET, ROW, and MIN functions being embedded into the SUMPRODUCT function. Whoa! Right? Let's break this down.

SUMPRODUCT works like this:

=SUMPRODUCT(<Range1>,<Range2>,[<Range3>...])

The values in each row of each range are multiplied together and then added up. In its simplest form, it's a great way to calculate things like weighted averages. The problem is that is will include every row in the ranges, even those hidden due to filtering or otherwise.

What do we know that doesn't include hidden rows? That's right SUBTOTAL has options to exclude hidden rows. Using subtotal type "109" is a sum that ignores hidden rows. So here's how to incorporate SUBTOTAL with OFFSET and MIN to make this work:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1)),<Range2>)

The crazy OFFSET, ROW, MIN combo is a way to coerce Excel to treat each cell in Range1 individually, like it does automatically when simply referencing the range in SUMPRODUCT, while still having the doesn't-count-hidden-rows benefit of SUBTOTAL type 109.

It also occurred to me that many Excel users may be using SUMPRODUCT to perform summing of data based upon multiple criteria -- you know, SUMIFS before Excel 2007 came out and we had SUMIFS. Here's how you'd do that using this technique:

Doesn't exclude hidden rows:

=SUMPRODUCT((<Range1>)*(<Range2>="West")*(<Range3>="Product A"))

This would add up "Sales" (Range1) where the "Region" (Range2) is equal to "West" and the "Product" (Range3) is equal to "Product A".

Excluded hidden/filtered rows:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1))*(<Range2>="West")*(<Range3>="Product A"))

I hope this explanation has been helpful. Excel_Geek Insiders subscribers, I'll fire you off a sample spreadsheet illustrating these techniques.

Later,

Excel_Geek