Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
count specific value with filtered data | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Charting Filtered Data | Charts and Charting in Excel | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |