Subtotal Function

[tamaramb]

Hi, can anyone please tell me if there is an add on to the SUBTOTAL function to stop the result from changing when using filter?

For example, I have filtered and hidden selective rows on my worksheet and used the =SUBTOTAL(109, range) function which gives me the correct result at first however when I then change my filter on the worksheet the value changes to an incorrect number.

Thanks
Tamara

[humdingaling]

not quite sure what you are trying to do?

you want to sum on a specific filter?

[tamaramb]

Hi, yes sorry for the confusion. I'm trying to sum on both hidden and filtered rows - I am using =SUBTOTAL(109, range) which works however once I change my filter the value within the cell with this formula also changes, so I'm looking to see if there is a way that the value can be fixed when the filter is used.

[humdingaling]

you cant have a cell both be fluid and static at the same time

if you want to keep the result of the filter you can copy and paste value elsewhere to record it

or

use a different formula based on filter criteria like sumif or sumproduct
however this method would mean that is based on a fixed set of criteria