How can I autosum only visible cells?

[EFM pauls]

I have hidden rows in a spreadsheet. When I autosum, the total includes the
hidden rows. Is there a way to only autosum the visible cells?

 

[Dave F]

This may work:

Select the column you're trying to autosum. Go to Edit--Go
To--Special--Visible Cells only. Click OK. Then click the autosum button.
What happens?

Dave

 

[Dave Peterson]

If the rows are hidden by data|autofilter, you could use:

=subtotal() instead of =sum()

If you're using xl2003, you could use =subtotal() no matter how the rows are
hidden.

=subtotal(9,a1:a10) 'any version of excel
or
=subtotal(109,a1:a10) 'xl2003 only

 

[Pete_UK]

Use SUBTOTAL(9,range) instead of SUM(range). You can change the number
to get different effects, like COUNT and COUNTA - see Excel Help for
further details.

I put totals like this on the top row of the spreadsheet with the
header row (and filter pull-downs) below them. This way you can see
them easily when applying filters, rather than jumping down to the
bottom of the sheet each time.

Hope this helps.

Pete