Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sums, Filters and Events oh my!
Okay fo starters I may very well be making this harder then it needs to
be. My overall goal is to sum up 4 columns and have that sum change and reflect the use of filters. Unfortunately I had no luck finding a way to have the Sum function ignore rows that were filtered out. So I wrote a little VB script that does this for me. What I would really like to do now is link that VB script to a 'filter event', so when you change the filter, the totals automatically update. There doesn't seem to be a filter event. I am sure once again I am missing something obvious. Thanks in advance for what I am sure will be a number of useful replies. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sums, Filters and Events oh my!
Your missing the subtotal worksheet function which will do what you want
without events or code. =subtotal(9,A1:A200) the first argument defines the math operation to perform. 9 is sum, 3 is count as examples. See Help in excel for details. -- Regards, Tom Ogilvy "Merlin" wrote: Okay fo starters I may very well be making this harder then it needs to be. My overall goal is to sum up 4 columns and have that sum change and reflect the use of filters. Unfortunately I had no luck finding a way to have the Sum function ignore rows that were filtered out. So I wrote a little VB script that does this for me. What I would really like to do now is link that VB script to a 'filter event', so when you change the filter, the totals automatically update. There doesn't seem to be a filter event. I am sure once again I am missing something obvious. Thanks in advance for what I am sure will be a number of useful replies. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sums, Filters and Events oh my!
Yep, that is what I needed, I knew I had to be missing something
obvious. Thanks a bunch. Tom Ogilvy wrote: Your missing the subtotal worksheet function which will do what you want without events or code. =subtotal(9,A1:A200) the first argument defines the math operation to perform. 9 is sum, 3 is count as examples. See Help in excel for details. -- Regards, Tom Ogilvy "Merlin" wrote: Okay fo starters I may very well be making this harder then it needs to be. My overall goal is to sum up 4 columns and have that sum change and reflect the use of filters. Unfortunately I had no luck finding a way to have the Sum function ignore rows that were filtered out. So I wrote a little VB script that does this for me. What I would really like to do now is link that VB script to a 'filter event', so when you change the filter, the totals automatically update. There doesn't seem to be a filter event. I am sure once again I am missing something obvious. Thanks in advance for what I am sure will be a number of useful replies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use "OR" in two auto-filters/custom filters? | Excel Discussion (Misc queries) | |||
Pivot Table filters, especially DATE filters | Excel Worksheet Functions | |||
How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
do events? background events | Excel Programming | |||
Filters, Subtotal & Intacted Results after the filters' Removal | Excel Discussion (Misc queries) |