Quick Navigation
Excel’s basic functions, like SUM, AVERAGE, COUNT, MIN and MAX are indispensable for harnessing the power of spreadsheets, but they don’t always work well with filters and structured reports. By default, these functions tally every cell in their ranges, regardless of whether the cells are filtered or hidden. Many times in reporting, it is useful to limit functions to only consider rows that are visible on the sheet and ignore the hidden or filtered values. SUM, AVERAGE, and the like can’t do this on their own. Fortunately, Excel has a function called SUBTOTAL that will consider only visible or filtered rows in its calculations, and it can perform all the operations mentioned and more! This tutorial will walk though the use of the SUBTOTAL to sum a filtered data table…
Examine the Data Set
First off, let’s take a look at our sample data…
We have basic sales data by Region, Sales Manager, and Year. Ideally, we’d like to be able to filter by Region to get a total for sales in that part of the country. Usually, it is easiest to filter data when it is in a data table, so let’s create one.
What if I wanted to show a rank of the filtered data?
ok, try adding a dummy column with a subtotal, example in row 10: =subtotal(103;a10)
now you have another criteria depending on the filter, so all multi-criteria formulas will work as well, far beyond the ones provided with subtotal!
enjoy…
hey it works brilliantly what you told, but if I drag the same formula for the entire Column, Excel doesn’t accept that.
It is like any other formula, copy the couple first ones so 103 doesn’t change, then pick them all and drag. It really wants no special treatment.
Spread the word and enjoy!!!
Hi There,
I’m trying to add a total from each separate tab on my spreadsheet to give a summary of the totals on a final tab, if for some reason one of the tabs is removed / deleted it throws out the formula on the final sheet as the tab its looking for no longer exists. How do I tell Excel to ignore deleted tabs?
Any help would be appreciated, I am afraid I’m a novice on excel and just getting to grips with the basics it seems!
Thanks
Sam
hi, although out of topic I’ll give a simple and effective answer:
rap individual sums in IfError()
example instead of: =sum(Sheet1!B11,Sheet2!B11)
write: =sum(IFERROR(Sheet1!B11,0),IFERROR(Sheet2!B11,0))
and so on…
Is it possible to have a separate tab that would show the running total for each region? So if you added something onto East it would automatically add it on and update the other tab? Or would you need to have totals on that page for it to work?
Hi,
I want to show the filtered value in a separate cell.. say from the above example if we filter data on “WEST”, i want “WEST” to be shown in separate cell (dynamically, that cell should show filtered data what is filtered). Glad if this is possible thru formulas (not VBA)
Prasad
Excellent Example ….Solved my problem
Hello there..kindly help me on this: I want to grade only 7 subjects out of 11. 4 are core the rest are optional and a student must therefore till they’re a minimum of 7. I will be glad if you reply to my email too.
I want to use the AVERAGEIF, SUMIF AND COUNTIF functions but want them to only apply to the visible data on a filtered table. How do i do this?
if i want to skip “William Foster” in above table and subtotal automatically skip “William Foster” and add “Robert” to do the sum of last 4 managers…..
is it possible??
I have a spreadsheet that has had a subtotal in it from the beginning. Every time that I filtered the data, the subtotal would remain at the bottom of the column and sum only the filtered data. All of the sudden, the subtotal stopped being visible when I filter the data. What can I do to make the subtotal always visible? I have tried adding a blank line between the data and the subtotal and that did not work. I have jury rigged it by putting Total in the Total role in the bottom of my filtered column so that i can select it with my filter. Do you have any suggestions on how I can correct this?