# Thread: Auto Sum with Filter (Excel '03 & '07)

1. ## Auto Sum with Filter (Excel '03 & '07)

I have a spreadsheet with several columns. I am using Filtering to sort out the columns either by rep name, date of activity, or amount of production and or several other factors. What I want to do is to have the columns auto sum the amount of production, and then after I filter them auto sum again to that particular filter. Is that possible?

2. ## Re: Auto Sum with Filter (Excel '03 & '07)

The SUBTOTAL function can calculate the statistical functions on filtered data. You can read about it in the HELP

[I find putting some of the stats above the header row and then freezing the panes at the header row works well. You can even include the normal functions to calculate the stats for the entire set so you can compare them]

Steve

3. ## Re: Auto Sum with Filter (Excel '03 & '07)

Let's say the amount of production is in column H, and that you'll have fewer than 1,000 data rows.

The formula =SUM(H2:H1000) will sum *all* amounts in column H.

The formula =SUBTOTAL(9,H2:H1000) will sum the visible amounts in column H, i.e. if you apply a filter, the formula will only sum the filtered items.

4. ## Re: Auto Sum with Filter (Excel '03 & '07)

Sorry Steve,
I went to the help in '07 and now I am confused even more.
Edited: I figured it out. On my trial sheet I entered the formula =SUBTOTAL(109,h2:H20), I then ran a ZIp Code filter, and the sum totaled correctly for just those Zip Code.

5. ## Re: Auto Sum with Filter (Excel '03 & '07)

Thanks Hans, I just edited my reply to Steve letting him know that I had figured it out. I used 109 so that the hidden values wont be calculated just the visible ones.

6. ## Re: Auto Sum with Filter (Excel '03 & '07)

Just to clarify, using either 9 or 109 as the first argument will not include rows hidden by the filter. The difference between the two is that 109 will ignore manually hidden rows too, whereas 9 will include them.
FWIW.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•