SUM of or AVE of a range that has hidden ROWS

[agerrard]

Hi All,

How do you get Excel to only SUM or AVERAGE the rows that remain visible?

I have a filter (1 = yes and 0 = no) so i sorted using the 1 so all the rows that had a 1 in them are showing, but when i go to SUM or AVERAGE the range excel keeps adding the values in the hidden rows as well.

How do i prevent this??

Thanks!

 

[T. Valko]

Hi All,

How do you get Excel to only SUM or AVERAGE the rows that remain visible?

I have a filter (1 = yes and 0 = no) so i sorted using the 1 so all the rows that had a 1 in them are showing, but when i go to SUM or AVERAGE the range excel keeps adding the values in the hidden rows as well.

How do i prevent this??

Thanks!

Try these...

For the average:

=SUBTOTAL(1,A5:A100)

For the sum:

=SUBTOTAL(9,A5:A100)

Adjust the ranges to suit.

 

[dk]

Hi

You can use the SUBTOTAL function:

=SUBTOTAL(109,A1:A10) will sum only the visible rows in A1:A10
=SUBTOTAL(101,A1:A10) will give the average of just the visible rows in A1:A10

There are a number of functions available with SUBTOTAL - take a look in the Excel help for the full list.

HTH
DK

 

[agerrard]

Thanks T. That worked a treat.

By the way how does that work??

 

[T. Valko]

Thanks T. That worked a treat.

By the way how does that work??

The SUBTOTAL function was made specifically for filtered data. It does the calculation but ignores the rows hidden by the filter.

It can perform a variety of calculations. See Excel help for descriptions of all the calculations it can perform.