Friday, November 05, 2010

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

5 comments:

Anonymous said...

Very useful excel guide. Learnt a lot of things in excel.I was struggling with this transforming a SUMPRODUCT formula problem. Now after seeing your post, it get resolved.Please keep posting more and more.

http://godwinsblog.cdtech.in/2010/12/microsoft-office-excel-add-in-plug-in.html

Danny London said...

I liked that and it got me thinking.

A simple way to achieve the same result is to use Subtotal with option 3 which is Counta against a cell in your row which is always populated. This formula will return zero when the row is hidden. You can then use this in a Sumproduct to zero out the hidden rows.

Anonymous said...

@Excel_Geek :

Thanks a ton for the formula.. :) It worked perfectly for me.

Unknown said...

Haha... I usually get this stuff, but I'm stumped about just why this works. But it does. Genius.

In my case I'm trying to subtotal the absolute values of a column that is often filtered.

It sounds easy, but it's been impossible to do without helper cells: because SUBTOTAL (which knows about hidden rows) doesn't resolve to an array, so putting it inside SUMPRODUCT doesn't help.

I'll share my code in case it helps anyone.

With some test values (both negative and positive) in T11:T15, this formula does a nice job of multiplying each value by -1 if it's negative, turning them all positive...

=SUMPRODUCT(--T11:T15,T11:T15/ABS(T11:T15))

...and then of course it sums the list. FYI: T11:T15/ABS(T11:T15) just returns a -1 for negative numbers, 1 for positive.

But that didn't work for a filtered list. I tried wrapping my ranges in SUMPRODUCT and AGGREGATE in all kinds of ways till I needed to come up for air.

It's messy looking, but applying your formula was easy:

=SUMPRODUCT(--SUBTOTAL(109,OFFSET(T11:T15,ROW(T11:T15)-MIN(ROW(T11:T15)),,1,1))*(T11:T15/ABS(T11:T15)))

Note that in my case, there is only . The second part of SUMPRODUCT is my calculation on the same range.

Excel_Geek said...

Thanks for sharing @Unknown!