Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of visible cells with multiple filters - Office 2007
I'm trying to make a commission schedule with basic setup like as follows:
A B C D E 1 Date Client Type Pay Tax 2 12 Jun Smith A 100 10 3 19 Jun Jones B 240 24 4 21 Jun Frost A 180 18 5 23 Jul Kris C 500 50 etc etc Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June clients who are type A. This would reveal Rows 2 and 4. I want a cell that I can label 'Total Pay' that will only show the sum of visible cells which would be Rows 2 & 4 and would therefore show 280 Currently I am using =sum(D2:D5) which includes all cells whether they are visible or not. I read the 'ignoring visible cells' article but that didn't work. Can someone tell me how to get a some of the filtered cells ONLY? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of visible cells with multiple filters - Office 2007
=subtotal(9,D2:D5)
If you have an active filter and use the Autosum button, you'll get that subtotal function where you'd normally get a sum function. "Bruce" wrote: I'm trying to make a commission schedule with basic setup like as follows: A B C D E 1 Date Client Type Pay Tax 2 12 Jun Smith A 100 10 3 19 Jun Jones B 240 24 4 21 Jun Frost A 180 18 5 23 Jul Kris C 500 50 etc etc Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June clients who are type A. This would reveal Rows 2 and 4. I want a cell that I can label 'Total Pay' that will only show the sum of visible cells which would be Rows 2 & 4 and would therefore show 280 Currently I am using =sum(D2:D5) which includes all cells whether they are visible or not. I read the 'ignoring visible cells' article but that didn't work. Can someone tell me how to get a some of the filtered cells ONLY? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of visible cells with multiple filters - Office 2007
Thanks bpeltzer you're a lifesaver!
"bpeltzer" wrote: =subtotal(9,D2:D5) If you have an active filter and use the Autosum button, you'll get that subtotal function where you'd normally get a sum function. "Bruce" wrote: I'm trying to make a commission schedule with basic setup like as follows: A B C D E 1 Date Client Type Pay Tax 2 12 Jun Smith A 100 10 3 19 Jun Jones B 240 24 4 21 Jun Frost A 180 18 5 23 Jul Kris C 500 50 etc etc Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June clients who are type A. This would reveal Rows 2 and 4. I want a cell that I can label 'Total Pay' that will only show the sum of visible cells which would be Rows 2 & 4 and would therefore show 280 Currently I am using =sum(D2:D5) which includes all cells whether they are visible or not. I read the 'ignoring visible cells' article but that didn't work. Can someone tell me how to get a some of the filtered cells ONLY? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - cell borders not visible when cells colored | Excel Discussion (Misc queries) | |||
Go To Visible Cells - In Excel 2007 | Excel Discussion (Misc queries) | |||
office 2007 opening multiple files in error | Excel Discussion (Misc queries) | |||
Quick access toolbar in Excel (Office) 2007 - multiple lines possible? | Excel Discussion (Misc queries) | |||
Pasting multiple cells using Office Clipboard | Excel Discussion (Misc queries) |