View totals whilst filtering data

[richanor]

Hi

I have a sheet with around 5000 lines of data across columns A:N. Each line belongs to one of 15 geographical regions, and this is entered into column A. I have also applied a filter, so that I can just view one geographical region at a time.

Column N contains the number of units sold, and I have summed the total units sold in cell O1 using the formula =SUM(N1:N4930).

I was wondering if there is any way I can get this total to adjust when I apply a filter to column A e.g. when I select 'south' from the filter in column A - I will only see the sales for the south.

I hope I have explained this well enough, and would appreciate any advice

Thanks

Rich

 

[hiker95]

richanor,

Search in Excel Help for SUBTOTAL.

The 109 means SUM all rows in the filtered range, and, ignore hidden rows.


Try:
=SUBTOTAL(109,N1:N4930)

or:
=SUBTOTAL(109,N2:N4930)

 

[taigovinda]

One additional suggestion... it may seem counter-intuitive, but I most often find it's easier to put subtotals at the top of the page instead of the bottom. That way you don't have to move them when your data grows, and you can freeze panes so that you always see the subtotals. In that case, you could change n1:n4930 to be n3:n65536, where line 1 is the subtotal and line 2 is a blank row.

 

[richanor]

Excellent! Thankyou.