Totals reflecting filtered cells only - not all data in worksheet

M

Mark

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?
 
M

Mike Lewis

Try the subtotal function:

=SUBTOTAL(9,F12:F22)

It will total only visible cell

Good Luck

Mike
 
M

Mark

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
 
M

Mike Lewis

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" <[email protected]> wrote
Click to expand...
in message [email protected]>...
 
M

Mark

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
 
Top