Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
I am having difficulty applying sub-total to a filtered worksheet. My
worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
It's not clear what you want to do.
The calculations are on text in ColK and need to give a total utilizing the words in parenthese. Ok, so just post an example of what's in column K (only, the other columns are irrelevant) and what exaclty you want to count. -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... I am having difficulty applying sub-total to a filtered worksheet. My worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
Yes the calculations are on text. So in a large spreadsheet that has over
5000 records, we filter down for a weekly report, a monthly report and a status (pending, denied, approved). In the reports we need to know how many "Utility" or "Design" submittals. ColK Control Zone Use (Utility) Stopping Sight Distance (Design) Clear Zone (Utility) Clear Zone (Utility) Bridge Width (Design) Shoulder Width (Design) Structural Capacity (Design) Vertical Alignment (Design) Design Speed (Variation) Thanks. "T. Valko" wrote: It's not clear what you want to do. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. Ok, so just post an example of what's in column K (only, the other columns are irrelevant) and what exaclty you want to count. -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... I am having difficulty applying sub-total to a filtered worksheet. My worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
Ok, assume the full unfiltered range is K5:K100.
Use a cell to hold the criteria to count for: A1 = Design =SUMPRODUCT(SUBTOTAL(3,OFFSET(K5:K100,ROW(K5:K100)-ROW(K5),0,1)),--(ISNUMBER(SEARCH("("&A1&")",K5:K100)))) -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... Yes the calculations are on text. So in a large spreadsheet that has over 5000 records, we filter down for a weekly report, a monthly report and a status (pending, denied, approved). In the reports we need to know how many "Utility" or "Design" submittals. ColK Control Zone Use (Utility) Stopping Sight Distance (Design) Clear Zone (Utility) Clear Zone (Utility) Bridge Width (Design) Shoulder Width (Design) Structural Capacity (Design) Vertical Alignment (Design) Design Speed (Variation) Thanks. "T. Valko" wrote: It's not clear what you want to do. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. Ok, so just post an example of what's in column K (only, the other columns are irrelevant) and what exaclty you want to count. -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... I am having difficulty applying sub-total to a filtered worksheet. My worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
You ROCK!!!!!! This worked withought any problems.
"T. Valko" wrote: Ok, assume the full unfiltered range is K5:K100. Use a cell to hold the criteria to count for: A1 = Design =SUMPRODUCT(SUBTOTAL(3,OFFSET(K5:K100,ROW(K5:K100)-ROW(K5),0,1)),--(ISNUMBER(SEARCH("("&A1&")",K5:K100)))) -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... Yes the calculations are on text. So in a large spreadsheet that has over 5000 records, we filter down for a weekly report, a monthly report and a status (pending, denied, approved). In the reports we need to know how many "Utility" or "Design" submittals. ColK Control Zone Use (Utility) Stopping Sight Distance (Design) Clear Zone (Utility) Clear Zone (Utility) Bridge Width (Design) Shoulder Width (Design) Structural Capacity (Design) Vertical Alignment (Design) Design Speed (Variation) Thanks. "T. Valko" wrote: It's not clear what you want to do. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. Ok, so just post an example of what's in column K (only, the other columns are irrelevant) and what exaclty you want to count. -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... I am having difficulty applying sub-total to a filtered worksheet. My worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter Subtotal Problems
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jarnell" wrote in message ... You ROCK!!!!!! This worked withought any problems. "T. Valko" wrote: Ok, assume the full unfiltered range is K5:K100. Use a cell to hold the criteria to count for: A1 = Design =SUMPRODUCT(SUBTOTAL(3,OFFSET(K5:K100,ROW(K5:K100)-ROW(K5),0,1)),--(ISNUMBER(SEARCH("("&A1&")",K5:K100)))) -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... Yes the calculations are on text. So in a large spreadsheet that has over 5000 records, we filter down for a weekly report, a monthly report and a status (pending, denied, approved). In the reports we need to know how many "Utility" or "Design" submittals. ColK Control Zone Use (Utility) Stopping Sight Distance (Design) Clear Zone (Utility) Clear Zone (Utility) Bridge Width (Design) Shoulder Width (Design) Structural Capacity (Design) Vertical Alignment (Design) Design Speed (Variation) Thanks. "T. Valko" wrote: It's not clear what you want to do. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. Ok, so just post an example of what's in column K (only, the other columns are irrelevant) and what exaclty you want to count. -- Biff Microsoft Excel MVP "Jarnell" wrote in message ... I am having difficulty applying sub-total to a filtered worksheet. My worksheet has 11 columns (6 shown in the example below). At the bottom of the worksheet I need to be able to have multiple calculations run based on the filters. The calculations are on text in ColK and need to give a total utilizing the words in parenthese. These calculations need to update automatically when the filter is changed on ColD or on ColJ (status). I am at a loss. ColA ColB ColC ColD ColE-ColJ ColK 2244 1 0 12/3/09 Misc. Info Control Zone Use (Utility) 2246 1 0 12/17/09 Clear Zone (Utility) P2242 4 0 12/1/09 Superelevation (Design) 2215 2 1 12/9/09 Vertical Clearance (Design) . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal and Auto-filter | Excel Worksheet Functions | |||
SUBTOTAL and FILTER | Excel Worksheet Functions | |||
excel subtotal problems | Excel Worksheet Functions | |||
Auto Filter + subtotal | Excel Discussion (Misc queries) | |||
Auto Filter + subtotal | Excel Worksheet Functions |