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

#1
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 •

#2
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 •

#3
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 14 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

#4
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 •