Autosum with Autofilter

[Peter J Elliott]

I have a spreadsheet with numerous values in one column and different
identifying data in other columns.

I can sort the data nicely using autofilter, however, my autosum at the
bottom of the values column continues to display the total value of all the
rows, not just the ones that I have filtered to view.

I need to be able to filter the spreadsheet and to have it autosum only
those rows in view on screen. Is this possible?

Many thanks

Peter

 

[Ken Wright]

Use the SUBTOTAL() function which will work only on filtered rows.

 

[Ken Wright]

Although in 2003 you have the option for it to also discount hidden rows as
well.

 

[A.W.J. Ales]

Peter,

Use the function Subtotal(9;columnrange)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Peter J Elliott]

Thanks Ken

Regards

Peter

Although in 2003 you have the option for it to also discount hidden rows as
well.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

 

[Debra Dalgleish]

As other have suggested, use the Subtotal function to sum only the rows
visible after filtering.

Excel will create this for you automatically --

Create your list, then select a cell in the list
Choose Data>Filter>AutoFilter
Filter one of the columns
To sum a column, select a cell in the row directly below that column.
(or leave one blank row between the table, and the grand total)
Click the AutoSum button, and Excel will enter a Subtotal formula.
Press Enter to complete the formula

 

[Peter J Elliott]

Thanks all - that simple eh?

Cheers

Peter

As other have suggested, use the Subtotal function to sum only the rows
visible after filtering.

Excel will create this for you automatically --

Create your list, then select a cell in the list
Choose Data>Filter>AutoFilter
Filter one of the columns
To sum a column, select a cell in the row directly below that column.
(or leave one blank row between the table, and the grand total)
Click the AutoSum button, and Excel will enter a Subtotal formula.
Press Enter to complete the formula