Denice, an Excel School student emailed me an interesting problem.
I have a bunch of data from which I want to find the sum of values that meet a criteria. But I also want to exclude any rows that are hidden.
Well, we know how to find sum of values that meet a criteria – we use either SUMIF, SUMIFS or SUMPRODUCT formula.
We also know how to find the sum of values while excluding hidden rows – we use SUBTOTAL Formula
But sum of values meeting a criteria and not in a hidden rows?!?
Of course, we can get such a total in excel, we just need to mash up SUBTOTAL Formula with SUMIFS (or SUMPRODUCT) and add a dash of coffee to it.
Step 1:Add an extra column to your data
Our first step is to find which rows are hidden and which are not. We can do this using SUBTOTAL Formula.
Assuming your data is in the range A2:B15
Add an extra column next to your data and write the formula =SUBTOTAL(102,B2). This formula will return “1” if cell B2 is visible and “0” if hidden.
Now drag the formula to fill rest of the cells in the extra column. At this point our data table should look like this:
Step 2 [Excel 2007+]: Write the SUMIFS Formula
Now, our summing criteria is very simple. We want to find the sum of all values where product=”Pod Gun” and Visible?=”1″
The formula is =SUMIFS(B2:B15.A2:A15,"Pod Gun",C2:C15,1)
If you do not get this formula, take a sip of that coffee and look long and hard it. If you need some clues, check out the Introduction to SUMIFS Excel Formula page.
Step 2 [All versions of Excel]: Write the SUMPRODUCT Formula
Just in case you do not have Excel 2007 or above, you have to write SUMPRODUCT formula instead of SUMIFS. Here is the formula:
=SUMPRODUCT((B2:B15)*(A2:A15="Pod Gun")*(C2:C15=1))
Again, take a sip, widen your eyes and try to gaze seriously at the pixels. Here is the Introduction to SUMPRODUCT Excel Formula page if you need help.
Step 3: Finish the coffee before it gets cold.
or if you are drinking cold coffee, finish it before it tastes funny.
Download Example Workbook:
Here is the example workbook. Download and play with it to learn.
Share your tips & experiences:
I use “hide rows” option almost regularly to remove un-necessary info. from view. But I never had the need to exclude the values in hidden rows from my formulas.
What about you? How have you handled similar problems before?
19 Responses to “Exclude Hidden Rows from Totals [How to?]”
Chandoo...I think you mean =SUBTOTAL(102,b2) for the extra column with the subtotal formula, otherwise you'll get a circular reference error.
Also, you can do away with the extra column just by entering this one formula somewhere:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A15,ROW(A2:A15)-ROW(A2),0,1)),--(A2:A15="Pod gun"),B2:B15)
I got this from http://www.excelforum.com/excel-worksheet-functions/394632-how-do-you-ignore-hidden-rows-in-a-countif-function.html
I forgot that your wordpress blog turns two minus signs into one, if you don't leave a space between them. Here's that formula again, with a space between the minus signs (which excel happily ignores)
=SUMPRODUCT(SUBTOTAL(103,
OFFSET(A2:A15,ROW(A2:A15)-ROW(A2),0,1)),
- -(A2:A15="Pod gun"),B2:B15)
Also a shout out should go to Bob Phillips for this answer...his work, not mine.
@Jeff. you are right.. I mean SUBTOTAL(102,b2)...
Thank you so much for the one big formula. I tried to add SUBTOTAL to SUMPRODUCT but failed ..I didnt know about the offset( rows() thingie.. Thanks for the link too...
chandoo ....i was also trying to merge subtotal and sumif and came across this all big formulas with sumproduct and offset but was to complecate for me to understand..finally thanks to you i got my work done with extra colum subtotal and sumifs.....simple and sweet...thank you
Hi Chandoo,
I didn't get that formula at first and I'm not a coffee drinker but I finally got it; there's a small typo in your formula :
" The formula is =SUMIFS(B2:B15.A2:A15,"Pod Gun",C2:C15,1) "
it should be :
" The formula is =SUMIFS(B2:B15,A2:A15,"Pod Gun",C2:C15,1) "
(comma after B2:B15).
Keep up the good work! Cheers!
Unless, I'm reading with question wrong, there is another quick way to subtotal visible cell. I use pivottables and filter for what I need.
Shorusan
Shorusan: you're right, that's a much simpler solution.
Using the formula appoach above, here’s a screenshot of the entire list, with no items hidden and with “Spit bomb” selected as the filter item in cell E1:
http://screencast.com/t/YWE2YjQ1YT
Note the total is 789
If we hide row 8 ( a “Spit bomb” that perhaps a customer returned because of lack of sufficient spit), then the total changes to 612
http://screencast.com/t/NDMzZTZhMm
Now here’s the list as a pivot table:
http://screencast.com/t/NGUwM2ZlMDE
I added a “Invoice Number” column, so we can filter out a particular item. Here’s how it looks with that faulty spit-bomb filtered out:
http://screencast.com/t/ZDFlZWNhY
As per the other method, we see the total for Spit Bombs changes to 612.
Hi Chandoo,
I am using Excel 2000 (pre-historic) and this does not work. SUBTOTAL on recognises function_number 1-11, and only ignore hidden rows only on filtered data.
I will attempt to see if this problem can be tackled in this version od Excel.
Thanks for all you very interesting posts.
I just want to thank everyone for their comments and also thank Chandoo for his help and creating the blog.
I am in Payroll and have to pay what I call "small accounts" i.e deductions. All the information is in one sheet, so when I filter the information, and do the Sumifs, it adds the hidden rows as well.
So once again thanks for all your input.
[...] many other times, Chandoo has a clever answer, utilizing SUMPRODUCT and a special 0/1 [...]
I had to read this and a couple more of your articles (e.g. http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/ ) in order to understand this, but I finally did. Thanks for the simple (and awesome) tutorial!
Chandoo:
I was intrigued by your simple SUBTOTAL formula to determine if a row is visible in a table. However, I have found that the formula causes unexplained errors in Excel Tables. With the formula included as a calculated field in a Table, refreshing the table will cause additional rows to be exposed in violation of the filtering applied to other columns. I have replicated this on multiple machines and in multiple data sets stored as Tables.
To demonstrate this, add the Visible formula to a Table connected to an external data source using ODBC. Then, filter one of the columns. Add a Total Row and Count the records in a any column. Now Refersh the table multiple times and you will see records creep into the table in violation of the filter set. Without the calculated field this does not happen.
Have I misunderstood some basic concept that would cause this behavior.
=LA=
Chandoo:
This is a follow up to my previous post. I surrounded your formula with the SUM function and and the error disappeared.
=SUM(SUBTOTAL(103,A2))
The column I was counting was a text column, hence the use of subfunction 103 instead of 102.
I cannot explain this! We are now entering the Twilight Zone!
=LA=
LA:
Thank you for this fix. I read this article thinking I had a great solution to table filtering and formulas based on the filtered set. But, then the weirdness started and I couldn't figure out a way around it w/o removing the subtotal function from the table rows. Glad I came back here and read the comments 🙂
There is an easier/simpler alternative:
Do away with the extra column just type this to your TOTAL Cell:
SUBTOTAL(107.B2:B10)
the second parameter B2:B10 is the range of the cells we want to SUM etc.
the first parameter code we used 107 because:
1 - SUM, include hidden
2 - SUBTRACT, include hidden
3 - MULTIPLY, include hidden
4 - DIVIDE, include hidden
107 - SUM range, ignore hidden cells
108 - SUBTRACT range, ignore hidden cells
109 - MULTIPLY, ignore hidden
110 - DIVIDE, ignore hidden
Jes:
Thanks for your response, BUT... I don't need totals. I needed a means of filtering the data to just the visible rows. The formula that I posted in my follow up gets the job done.
The need for the formula to identify visible rows is because reports built on Tables using the conditional summarization functions (SUMIFS, COUNTIFS, etc.) reflect ALL data in a Table, not just the visible rows. If you set MAJOR filters at the table level, and then use MINOR filters in the SUMIFS function (to simplify the formula), the summary total will nonetheless reflect all data in the table. The MAJOR filters have no effect. To get around this limitation, use the formula in my follow up and use the field in the SUMIFS function. Then the MAJOR filters will have affect.
Again, thanks for your response.
=LA=
LA, I encountered the same 'Twilight Zone' problem where, once I added a Visible column to my formatted table (Excel 2010), column filters started acting strangely, displaying rows it shouldn't have based on the filter.
I tried wrapping the SUBTOTAL formula in the Visible column with SUM(), but that made no difference.
I finally gave up, deleted the Visible column and went with the more complex formula that included the OFFSET function.
Anyone else run into this case where column filters display rows it shouldn't in a table that includes cells with SUBTOTAL formulas?
One more thing...
After I deleted the Visible column, filtering was still broken (displaying rows it shouldn't), even if I cleared all my filters and then re-applied them.
Whatever this bug is, once it's 'activated' it stays that way.
I finally 'deactivated' the bug by converting my table to range, then re-converting it back to to a formatted table. Once I did that, my filters displayed the expected rows.
[…] Exclude hidden rows in Conditional SUM […]