Quick Navigation

## Using SUBTOTAL to SUM a Filtered Table

Let’s try replacing **SUM** with **SUBTOTAL** in the **Total** row of the table. First, we should remove the filter on **Region** so we can work with the entire table at once. Click on the filter icon by the **Region** header, choose the **Select All** option in the drop-down menu, and click **OK**.

The **SUBTOTAL** function in Excel works as a kind of master function for many others. It can perform the same function as **AVERAGE**, **COUNT**, **COUNTA**, **MAX**, **MIN**, **PRODUCT**, **STDEV**, **STDEVP**, **SUM**, **VAR**, and **VARP**, all while taking into consideration hidden and filtered rows.

The syntax for **SUBTOTAL** is as follows:

=SUBTOTAL(function_num,ref1,[ref2],...)

The *function_num* indicates which type of calculation you would like to perform. It has two sets of values. One- and two-digit values calculate only values that show in the filtered table, but they *also* include any manually hidden rows in the spreadsheet. Three-digit values indicate that SUBTOTAL should ignore both filtered rows *and* any rows that are manually hidden. In other words, with three-digit values, what you see is what you get.

A summary of the potential values of *function_num* is below:

In this example, we want the **SUM** calculation from **SUBTOTAL**. Since we are only dealing with filtered rows and not hidden rows, both **9** and **109** will work equally well. Let’s replace the calculations in the **Total** row.

Underneath the column for each year, type the **SUBTOTAL** function, indicate **9** for the *function_num,* and select the range of cells in the table for the corresponding year. The data table will convert that cell range (normally, **D3:D28**) to it’s Data Table range name. Repeat the process for each year’s column.

Re-filter the data, and you’ll find that the **Total** row re-calculates to provide the correct values! **SUBTOTAL** can just as easily be used to compute averages, find the maximum or minimum values among those on display, and more!

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?