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?

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_num109 tells the formula to sum only the visible values in the range.

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?

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.

Thanks. I figured it out. I didn't have the correct row #s included in the formula. Now it works.

Ask Your Question

Weekly Poll