Hello, so I was wondering if it is possible to auto create a new total when selecting certain data from a auto-filter. So for instance, if I select "John" in auto filter, only his account revenue shows, but there are no grand totals of revenue.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Auto-create total when filtered
- Thread starter droo09
- Start date
Weaver
Well-known Member
- Joined
- Sep 10, 2008
- Messages
- 5,197
Use the =SUBTOTAL() formula to only sum data in visible rows.
Say you have names in A2:A20 and revenue in B2:B20
in your sum (say B25)
=SUBTOTAL(109,B2:B20)
Without the filter, this will give you a grand total, when filtered, only the visible cells are summed.
Alternatively, you can select all your data and create a pivot table. Put the names in 'rows' or 'row labels' (depends on XL version) and the revenue amounts in the 'data' field. This will give you a sum for each name in the list.
HTH
Say you have names in A2:A20 and revenue in B2:B20
in your sum (say B25)
=SUBTOTAL(109,B2:B20)
Without the filter, this will give you a grand total, when filtered, only the visible cells are summed.
Alternatively, you can select all your data and create a pivot table. Put the names in 'rows' or 'row labels' (depends on XL version) and the revenue amounts in the 'data' field. This will give you a sum for each name in the list.
HTH
Upvote
0
Similar threads
- Question
- Question
