How do I calculate only visible data (not hidden rows)?

G

Guest

When I sort some data and hide rows or columns formulas are calculating using
the entire database (including hidden rows). I'd like to calculate only
'visibe' data.
 
P

Pete_UK

Use SUBTOTAL( ) to act on only visible rows. For example, the formula:

=SUBTOTAL(9,A1:A100)

(equivalent to SUM) will only add the values in A1:A100 which are
visible.

Other parameters can be used instead of 9 to change the function -
check out Excel Help for a list of them.

Hope this helps.

Pete
 
P

Pete_UK

Use SUBTOTAL( ) to act on only visible rows. For example, the formula:

=SUBTOTAL(9,A1:A100)

(equivalent to SUM) will only add the values in A1:A100 which are
visible.

Other parameters can be used instead of 9 to change the function -
check out Excel Help for a list of them.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

hidden rows problem 7
Calculation Time 2
=counta() unless row is hidden 5
Copying worksheets with formulas in hidden columns 2
Trying to calculate # of times text string is displayed 1
Custom Filter loses blank rows 2
How do I hide multiple rows under one row IN EXCEL? 2
Code to calculate only visible cells 1

Top