Software

How to sum values in an Excel filtered list

You can't use a SUM() function to sum a filtered list, unless you intend to evaluate hidden and unhidden values. Here's how to sum only the values that meet your filter's criteria.

Filters are a powerful and easy-to-use feature. Using filters, you can quickly limit data to just the records you need to see. Summing filtered records is another matter. You might try a SUM() function but you might get a surprise—well, I can promise you'll get a surprise.

The figure bellows shows a filtered list. You can tell by the row numbers to the left that many rows are hidden. (We'll skip how the actual filter works. To learn more about that, read How to use And and Or operators with Excel's Advanced Filter.

The next figure shows what happens when you try to sum the filtered values. You can easily tell that the result isn't correct; the value is too high, but why? The SUM() function is evaluating all the values in the range D14:D64, not just the filtered values. There's no way for the SUM() function to know that you want to exclude the filtered values in the referenced range.

The solution is much easier than you might think! Simply click AutoSum—Excel will automatically enter a SUBTOTAL() function, instead of a SUM() function. This function references the entire list, D6:D82, but it evaluates only the filtered values.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!
About SUBTOTAL()

Although the SUBTOTAL() function references the entire list of values in column D, it evaluates only those in the filtered list. You might think that's because of the first argument, the value 9. This argument tells Excel to sum the referenced values. The following table lists this argument's acceptable values:

Evaluates hidden values Ignores hidden values Function
1 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
4 104 MAX()
5 105 MIN()
6 106 PRODUCT()
7 107 STDEV()
8 108 STDEVP()
9 109 SUM()
10 110 VAR()
11 111 VARP()

At this point, you might be saying, Wait a minute! The value 9 is supposed to evaluate hidden values. Shouldn't the correct argument be 109? It's a valid question and I have an explanation, I just don't think it's a great explanation: SUBTOTAL() ignores rows that aren't included in the result of a filter, regardless of the argument you specify. It's a quirk—just one of those little details you need to know about the function. Whether you use 9 or 109, SUBTOTAL() will evaluate only the visible values—it will not evaluate hidden values.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
Heajah86
Heajah86

But when the filter is removed it sums up all the fields that were once hidden. Anyway to sum filtered cells and keep the same formula without individually selecting each cell?

lyambor
lyambor

Why not use a Pivot table with a report filter

kreniska
kreniska

I have a better explanation of the different between 9 and 109 for the dirst argument to subtotal. If you use 109 and then "hide" some rows, it will not sum the hidden rows. AutoFiltering is not the same as "hiding".

sean
sean

Using Office 2010, when using option 9 - it is the same as doing a sum for the cell group, in essesnce it does calculate the hidden values as well. If you want it to skip the hidden cells then use option 109. Excellent article though, this feature is great, especially as it dynamically updates the total based on the cells you hide/unhide.

venkat1926
venkat1926

it is good. but to use sum of a column of filtered range in vba use this sample code suitably modified Sub test() Dim r As Range, tot As Double Set r = Range("A1").CurrentRegion r.AutoFilter field:=1, Criteria1:="a" tot = WorksheetFunction.Sum(r.Columns("B:B").Cells.SpecialCells(xlCellTypeVisible)) MsgBox tot End Sub

jbenton
jbenton

...if you're criterion is recorded in C2 (just as the min value though, no qualifiers), then SUM((C2 < C6:C82)*D6:D82) will give you the answer WHEN ENTERED AS AN ARRAY FUNCTION (ie use ctrl-sh-enter) SUMPRODUCT will also work when not entered as an array The advantage of these over SUMIF is that you can use multiple and more complex conditions also, for years I tried to memorise that 9 meant sum, 1 meant average etc and kept opening Help to make sure. Until I realised that they're in alphabetical order - duh!

zgozvrm
zgozvrm

I'm still using version 10 (Excel 2002, SP3). It only offers functions 1 through 11

stapleb
stapleb

My concern would be the same as one or two other comments - as soon as the filter is removed the total changes. 2007 has the Function "Sumifs" which allows a number of criteria to be applied to different ranges and you will get the total required.

kknock
kknock

A better approach would be to create a summary report on a new tab and use the DSUM() function with an appropriate criteria range that specifies the value you filtered on. Then your sums are accurate no matter how you are viewing the data. To avoid having to change your range, create a dynamic named range using using the offset formula, e.g. =OFFSET('Sheet1'!$A$1,0,0,CountA('Sheet1'!$A:$A),COUNTA('Sheet1'!$1:$1))

martin.chresta
martin.chresta

If rows are explicitly hidden by selecting rows and then right-click Hide, you will see a difference between function 9 and function 109. When a filter is applied to the range, the function selected does not seem to matter. The subtotal is of all visible values regardless of whether some rows are hidden before or after filtering. Note also that applying a filter will unhide all rows in the range although they can be hidden after filtering.

dogknees
dogknees

There is a difference between a filtered list that doesn't show all values and "Hidden" rows. Using subtotal(9,...) Hidden rows(not filtered) are included in the total. Using subtotal(109,...) deosn't include them. Both versions ignore filtered rows. Regards

jbenton
jbenton

as indicated earlier, that autofiltering may unhide some of your hidden rows which will of course stay unhidden when you remove the filter

mbbccb
mbbccb

You miss the point, which is that if you change the filter the total also changes. If you select different criteria for the filter you would have to modify a sumif, whereas with the subtotal function it will automatically perform the specified calculation on visible values when you change the filter.

jrfrom
jrfrom

I just tested this, twice, and it... =SUBTOTAL(9,J43:J52) works for both "Hidden" and "Filtered" rows; neither included the "Hidden" or "Filtered" values in the total. I'm using MS Excel 2007; I'm not sure if this makes a difference, but often times it does.

Editor's Picks