Hi Martyn,

"2" is for COUNT, "9" is for SUM. Excel Help for SUBTOTAL worksheet
function will show you the other values you can use.

Hope this helps.

Pete

martyn wrote:
Thanks, I had found subtotal just after a posted the question and using
subtotal(2, range).

not sure what putting different numbers in does but it works

"Pete_UK" wrote:

Instead of using SUM(range) use SUBTOTAL(9,range) - this will show the
sum of the filtered values and will adjust when you change the filter.

If you have a long list, you might like to put the totals on the top
row (insert a new row 1 and cut/paste the total row to the new row 1).
If you also use Window | Freeze panes then you will always be able to
see your totals after you apply a filter.

Hope this helps.

Pete

martyn wrote:
I have what would appear to be a simple issue totaling filtered data as below

Dept count
NESAS 1
NESAS 1
blabla 1
3

Rate 22
Total 66
How can I get the above to work when I filter the data? For instance if I
filter by the code NESAS the sum should equal 2 NOT 3 and this would then
give total of 44