I found out instead of using the sum(), I should use the Subtotal() to get
the proper value of a summation over an autofilter result.

That said, I need to get the average of a column which has an autofilter
applied, which function should I use to do this?

Thank you,

QB

After some reading, I found out that the first input variable for the
SubTotal() control what type of value is returned (1- average, 9-sum). So
the one function does it all!

QB




"QB" wrote:

I found out instead of using the sum(), I should use the Subtotal() to get
the proper value of a summation over an autofilter result.

That said, I need to get the average of a column which has an autofilter
applied, which function should I use to do this?

Thank you,

QB

The same, SUBTOTAL. You just need to change the function number to either 1
or 101. The function number controls what type of operation you are
performing. Whereas
=SUBTOTAL(9,A:A)
means to sum,
=SUBTOTAL(1,A:A)
means to average.

See the XL help file for further details.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"QB" wrote:

I found out instead of using the sum(), I should use the Subtotal() to get
the proper value of a summation over an autofilter result.

That said, I need to get the average of a column which has an autofilter
applied, which function should I use to do this?

Thank you,

QB

use function 101 in your subtotal for average not including hidden rows. So,
=SUBTOTAL(101,A2:A500)

If you type =SUBTOTAL(, thin click the Fx button in your address bar, you
can click the "Help on this function" hyperlink for more options.

"QB" wrote:

I found out instead of using the sum(), I should use the Subtotal() to get
the proper value of a summation over an autofilter result.

That said, I need to get the average of a column which has an autofilter
applied, which function should I use to do this?

Thank you,

QB

Please keep in mind, option 1 is going to average hidden rows. 101 ignores
hidden rows.

"QB" wrote:

After some reading, I found out that the first input variable for the
SubTotal() control what type of value is returned (1- average, 9-sum). So
the one function does it all!

QB




"QB" wrote:

I found out instead of using the sum(), I should use the Subtotal() to get
the proper value of a summation over an autofilter result.

That said, I need to get the average of a column which has an autofilter
applied, which function should I use to do this?

Thank you,

QB