Sum up visible rows of column

[adam]

Hi,

The following formula sum ups all rows of column P=SUM(P:P) when placed in A1. How could it be made to sum up all the visible rows when placed in cell A1?

Any help on this would be kindly appreciated.

[HansV]

Are the rows hidden by a filter or by other means?

[rory]

If using 2003 or later:
=SUBTOTAL(109,P:P)

[adam]

Thanks for the reply Hans. Yes, the rows are hidden by a filter. And thanks for the formula Rory. your formula works fine.

[HansV]

Rory's formula works whether the rows are hidden by a filter or otherwise.

To ignore rows hidden by a filter, you can also use

=SUBTOTAL(9,P:P)

[adam]

The formula sum ups the column from row 1 and so on. How could it be made to sum up starting from row 17?

[HansV]

=SUBTOTAL(9,P17:P1000)

Adjust the 1000 to a number larger than the number of rows you expect to use.