Total a Filtered List in Excel

Total a Filtered List in Excel

In Excel, you might have a long list of orders with a grand total at the end. If you filter the Region column, so the list only shows one region’s sales, you’d like the total to include only those items. Here’s how to total a filtered list in Excel.

SUM Function Problem

If you used the SUM function in the grand total cell, the result won’t change if a filter is applied. This list is filtered to show orders from the West Region.

At a glance, you can see that the Grand Total is much higher than the records listed. There are only 3 orders visible, but the order count is calculated as 49.

AutoFilterWrong
Instead of SUM or COUNT, you can use the SUBTOTAL function, and only the filtered rows will be included in the grand total.

Create a SUBTOTAL formula

A quick way to create a SUBTOTAL formula is to:

  1. Apply a filter to the list. In this example, the Region column is filtered for “West”.
  2. Select the cell where you want the grand total.
  3. On Excel’s Standard toolbar, click the AutoSum button, or on the keyboard, press the Alt key and tap the equal sign key (Alt + =).

AutoSum
Because the list is filtered, a SUBTOTAL formula is inserted, instead of a SUM formula.

AutoFilterSubtotal

Reading a SUBTOTAL formula

Within the brackets for the SUBTOTAL function there are two arguments, separated by a comma (or a semi-colon, depending on your regional settings).

The first argument is a number that tells Excel which summary function to use in the subtotal. Most of the time you’ll use a 9, which is the SUM function.

The second argument is the range of numbers that should be subtotaled. In this example, cells H2:H50 are the cells that we want to sum.

Changing a SUBTOTAL formula

In some cases, you might want a different function in the SUBTOTAL function. For example, it wouldn’t make sense to sum the Unit Cost column, but it might be useful to know the average unit cost.

Unfortunately, when the list is filtered, only the SUM function on the AutoSum button inserts a SUBTOTAL function. If you click Average, you’ll get an AVERAGE formula. (Note: This is improved in Excel 2007, and the other functions on the AutoSum button also insert a SUBTOTAL function.)

AutoSumAvg
So, click Sum to create a SUBTOTAL function, then change the function number from 9 to 1, which will calculate an AVERAGE. Or, change the function number to 2, and you’ll calculate a COUNT of the numbers in a range.

There are 11 functions that you can use as the first argument in the SUBTOTAL function. The list is alphabetical, so that might help you remember some of the numbers, without going to Excel’s Help every time.

SubtotalFunctions

Ignore Manually Hidden Rows

In Excel 2003, and later versions, you can also use the numbers (101, 102…), as shown in the second column of the table below.

SubtotalNums
With these numbers, any rows that are hidden with the Hide command (Formatâ–ºRowâ–ºHide) will NOT be included in the subtotal.

If you use the numbers from the first column (1, 2…), any rows that are hidden with the Hide command WILL be included in the subtotal.

Note: Rows that you format to zero height WON’T be included in either type of subtotal.

More Information

There are other examples of formulas for summing and counting cells on my web site.

________________________

12 thoughts on “Total a Filtered List in Excel”

  1. Debra, this is nice. I never thought to click the AutoSum button when I wanted to use SubTotal. I just tried it in Excel 2007, and it has gained some smarts. Now, when you select a different function from the list (e.g., Average) it will insert the correct function number automatically. That saves having to look at the help file. It also uses the Table-style referencing if your AutoFilter is in a Table (List in Excel 2003).

  2. Hi Debra. It might be worth explicitly adding to your notes above that the Subtotal function does not ignore manually hidden columns. (Although your article only ever mentions using Subtotal for row stuff)
    From http://www.ozgrid.com/forum/showthread.php?t=87457 : In Excel 2003 hiding or unhiding rows will flag the selected rows as uncalculated, even if no rows were actually hidden or unhidden. If calculation is automatic this will trigger a recalculation. This behaviour is a change from previous versions. Hiding or unhiding columns does NOT flag the column as uncalculated.

  3. Hi, thats perfect and i dont know why i am not aware of this for all those years , but here is the question:
    If i want the subtotals at the top of each interval instead of having them below the intervals, what can i do?
    Thanks very much

  4. this is the way i get the filtered subtotal of a column (above the column header):
    =SUMPRODUCT((G$7:G$65533<0)*SUBTOTAL(9,OFFSET(G7,ROW(G$7:G$65533)-ROW(G7),)))
    i put this formula in cell G5. cell G6 holds the headers. cells G7 and onward down the column hold the data to sum.

  5. oh, i forgot that this was to subtotal only NEGATIVE values (to find backorders in a column). i’ve looked for this formula for ages and finally figured out how to do it. i hope this helps anyone else online who may need the same. i hope you don’t mind that i’m posting it here.
    this is the way i get the filtered NEGATIVE subtotal of a column (above the column header):
    =SUMPRODUCT((G$7:G$65533<0)*SUBTOTAL(9,OFFSET(G7,ROW(G$7:G$65533)-ROW(G7),)))
    i put this formula in cell G5. cell G6 holds the headers. cells G7 and onward down the column hold the data to sum.

  6. This was very helpful. Now for me to take this to the next level. EX: I have a monthly tab, this is for 5 divisions. These spreadsheets carry over the year to date totals. Now that you showed me how to sort and subtotal (by the way this is extremely helpful). Is there a way when we sort to have the divisional totals for the year to day in a simple step?

  7. I searched for several times for the formula I was looking for and I got it here. =SUMPRODUCT((G$7:G$65533<0)*SUBTOTAL(9,OFFSET(G7,ROW(G$7:G$65533)-ROW(G7),)))
    sheryl bradley thanks for the post

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.