sum a filtered column

Microsoft Office excel 2007 - upgrade
August 27, 2010 at 14:21:34
Specs: Windows XP
I have an excel file with monthly account sales history by item. There is a grand sum total for all accounts for each individual column of #s. When I filter the data by account, I would expect to see the sum total for that individual account in each column. Instead I still see the sum total for all accounts. How do I need to change this to only see the total for the account from the filter?

See More: sum a filtered column

Report •

August 27, 2010 at 17:36:07
Look up Insert Subtotals, About Subtotals and the SUBTOTAL function in Excel Help.

The SUBTOTAL function should get you what you want, but the Subtotals feature may give you more functionality.

=SUBTOTAL(109,A2:A10) will only sum the visible cells in A2:A10.

The function_num 109 tells the formula to sum only the visible values in the range.

Report •

August 30, 2010 at 12:51:10
Thanks, however, when I then change the filter to see a different customer with a different # of rows associated with it, the formula does not change accordingly. Is there a way to get the formula to adjust to the correct # of rows of information?

Report •

August 30, 2010 at 13:08:26
I'm not sure what you mean.

Here's my data set, with an AutoFilter set on A1:A13

     A          B
1  Name	     Amount
2  Steve        1
3  Bill         1
4  Tom          1
5  Steve        1
6  Tom          1
7  Tom          1
8  Ann          1
9  Tom          1
10 Tom          1
11 Bill         1
12 Steve        1
13 Steve        1
15	      12  <--- =SUBTOTAL(109,B2:B13)

When I filter on All, B15 shows 12, the SUM of all the values.
When I filter on Ann, B15 shows 1, since there is only 1 Ann.
When I filter on Steve, I get 4 since there are 4 Steve's.
2 for Bill, 5 for tom.

After applying a filter, =SUBTOTAL(109,B2:B13) only shows the SUM of the visible cells in B2:B13.

Report •

Related Solutions

August 30, 2010 at 13:13:21
Thanks. I figured it out. I didn't have the correct row #s included in the formula. Now it works.

Report •

Ask Question