Totals reflecting filtered cells only - not all data in worksheet

Hi

Under a range of data, I have cells with formulas or calculated totals
based on all of the data in that range. When trying to filter on this
data, I wish the results of the formulas/totals to change to refelct
only the data that has been filtered (the visible cells). Any
suggestions?

Try the subtotal function:

=SUBTOTAL(9,F12:F22)

It will total only visible cell

Good Luck

Mike
-----Original Message-----
Hi

Under a range of data, I have cells with formulas or
calculated totals
based on all of the data in that range. When trying to
filter on this
data, I wish the results of the formulas/totals to change
to refelct
only the data that has been filtered (the visible cells).
Any
suggestions?
.

Hi Mike

Not clear on how that may work. Have a number of columns on which to
filter on and some cells at base of data range with formulas that
account for all the data (not simply using count if or sum functions)
.. Wish the formulas to automatically change to reflect only the data
shown when filtered.

Any help appreciated.
Mark



"Mike Lewis" wrote in message ...
Try the subtotal function:

=SUBTOTAL(9,F12:F22)

It will total only visible cell

Good Luck

Mike
-----Original Message-----
Hi

Under a range of data, I have cells with formulas or
calculated totals
based on all of the data in that range. When trying to
filter on this
data, I wish the results of the formulas/totals to change
to refelct
only the data that has been filtered (the visible cells).
Any
suggestions?
.

Mark, the subtotal function is designed to sum only the
cells in the identified range that are visible, so if you
have a column of numbers, 5,10,15,20 and the 15 is hidden
for some reason - the result of =subtotal(9,range) would
be 35. The number 9 in the subtotal function tells it to
sum, other numbers in the subtotal function will count,
average etc. only the visible rows.

Mike

-----Original Message-----
Hi Mike

Not clear on how that may work. Have a number of columns
on which to
filter on and some cells at base of data range with
formulas that
account for all the data (not simply using count if or
sum functions)
.. Wish the formulas to automatically change to reflect
only the data
shown when filtered.

Any help appreciated.
Mark



"Mike Lewis" wrote
in message news:1c76b01c452f0$7c19a2a0
...
Try the subtotal function:

=SUBTOTAL(9,F12:F22)

It will total only visible cell

Good Luck

Mike
-----Original Message-----
Hi

Under a range of data, I have cells with formulas or
calculated totals
based on all of the data in that range. When trying to
filter on this
data, I wish the results of the formulas/totals to
change
to refelct
only the data that has been filtered (the visible
cells).
Any
suggestions?
.

.

Hi Mike

Thanks for your help. Unfortunately I don't think the subtotal
function can help in these circumstances, as not looking at simple
formulas like count, average etc, but will look at getting around it.

Thanks again
Mark


"Mike Lewis" wrote in message ...
Mark, the subtotal function is designed to sum only the
cells in the identified range that are visible, so if you
have a column of numbers, 5,10,15,20 and the 15 is hidden
for some reason - the result of =subtotal(9,range) would
be 35. The number 9 in the subtotal function tells it to
sum, other numbers in the subtotal function will count,
average etc. only the visible rows.

Mike

-----Original Message-----
Hi Mike

Not clear on how that may work. Have a number of columns
on which to
filter on and some cells at base of data range with
formulas that
account for all the data (not simply using count if or
sum functions)
.. Wish the formulas to automatically change to reflect
only the data
shown when filtered.

Any help appreciated.
Mark



"Mike Lewis" wrote
in message news:1c76b01c452f0$7c19a2a0
...
Try the subtotal function:

=SUBTOTAL(9,F12:F22)

It will total only visible cell

Good Luck

Mike
-----Original Message-----
Hi

Under a range of data, I have cells with formulas or
calculated totals
based on all of the data in that range. When trying to
filter on this
data, I wish the results of the formulas/totals to
change
to refelct
only the data that has been filtered (the visible
cells).
Any
suggestions?
.

.