Sums & Filtering

**danb001** · 11-29-2012, 07:50 PM

I have a spreadsheet with over 100 rows, with values in about 8 columns per row. I want to be able to add rows to this using sums, and still be able to filter the different columns in size order.

For example, Row 100 values will be a sum of Rows 10, 20 & 50. When I filter in size order for column F, for example, Row 100 now becomes Row 4, and Rows 10, 20 and 50 become rows 1, 2 and 3. I would like Row 4 to still be a sum of the values in Rows 1, 2 & 3 (previously 10, 20 and 50). At the moment I cant see a way of doing this as the sums do not pull through when filtering. Someone suggested using SUBTOTAL instead of SUM with no luck! :-/

Any help would be greatly appreciated!

Thanks!
Dan

**benishiryo** · 11-29-2012, 08:10 PM

hi Dan, welcome to the forum. dont seem to understand your question. since you mentioned Row 100 is already summing just Row 10, 20 & 50. i'm assuming you are doing SUM(A10,A20,A50)? that is assuming your figures are in Column A. so when you filter, it should be still summing these 3 amounts.

if the problem is with the total being sucked into the Autofilter, leave a space before doing a total. so if your data is from A1:H99, highlight just this range & do an Autofilter. do your SUM at A101

if you have many size orders & need the want the Autofilter to sum according to your size filter, then it should be:
SUBTOTAL(9,A2:A99)
again, leave a space & do the formula in row 101.

as you can see, i'm making a lot of assumptions. a sample Excel file would help. to upload a file, press "Go to Advanced" beside the "Post Quick Reply" button & click on the paperclip icon.

Sums & Filtering

LinkBack

Thread Tools

Rate This Thread

Display

Sums & Filtering

Re: Sums & Filtering

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions