|
Subtotal function with Filtered Data
I trying to use the following worksheet to analysis required and assigned
resources across a wide range of projects. Project Source Skill 3Q05 AWG 10G Assigned PL 0.00 AWG 10G Required PL 0.00 AWG 10G Gap PL 0.00 AWG 6G Assigned IDFM 0.00 AWG 6G Required IDFM 0.25 AWG 6G Gap IDFM 0.25 AWG 6G Assigned IDFT 0.25 AWG 6G Required IDFT 0.25 AWG 6G Gap IDFT 0.00 GigaDig 12G Assigned IDFM 0.00 GigaDig 12G Required IDFM 0.25 GigaDig 12G Gap IDFM 0.25 GigaDig 12G Assigned IDFT 0.20 GigaDig 12G Required IDFT 0.25 GigaDig 12G Gap IDFT 0.05 GigaDig 12G Assigned SLM-LSP 0.00 GigaDig 12G Required SLM-LSP 0.20 GigaDig 12G Gap SLM-LSP 0.20 Subtotal Assigned Subtotal Required Subtotal Gap Have read several discussions regarding use SUM(IF(FREQUENCY(NumRange,NumRange)0,1)) & SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)) functions however none seem to do what I want done. Goal is as I filter on a Project or Skill Type to have subtotals for Assigned and Required displayed at the bottom for the filtered data. -- RonB |
Ron,
Try this =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="Assigned")) -- HTH RP (remove nothere from the email address if mailing direct) "RonB" wrote in message ... I trying to use the following worksheet to analysis required and assigned resources across a wide range of projects. Project Source Skill 3Q05 AWG 10G Assigned PL 0.00 AWG 10G Required PL 0.00 AWG 10G Gap PL 0.00 AWG 6G Assigned IDFM 0.00 AWG 6G Required IDFM 0.25 AWG 6G Gap IDFM 0.25 AWG 6G Assigned IDFT 0.25 AWG 6G Required IDFT 0.25 AWG 6G Gap IDFT 0.00 GigaDig 12G Assigned IDFM 0.00 GigaDig 12G Required IDFM 0.25 GigaDig 12G Gap IDFM 0.25 GigaDig 12G Assigned IDFT 0.20 GigaDig 12G Required IDFT 0.25 GigaDig 12G Gap IDFT 0.05 GigaDig 12G Assigned SLM-LSP 0.00 GigaDig 12G Required SLM-LSP 0.20 GigaDig 12G Gap SLM-LSP 0.20 Subtotal Assigned Subtotal Required Subtotal Gap Have read several discussions regarding use SUM(IF(FREQUENCY(NumRange,NumRange)0,1)) & SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)) functions however none seem to do what I want done. Goal is as I filter on a Project or Skill Type to have subtotals for Assigned and Required displayed at the bottom for the filtered data. -- RonB |
Thanks for the help. In order to translat this to my worksheet, I interpret
the cell and range reference to C to be my Skill column with the example I provided. Is that correct? -- RonB "Bob Phillips" wrote: Ron, Try this =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="Assigned")) -- HTH RP (remove nothere from the email address if mailing direct) "RonB" wrote in message ... I trying to use the following worksheet to analysis required and assigned resources across a wide range of projects. Project Source Skill 3Q05 AWG 10G Assigned PL 0.00 AWG 10G Required PL 0.00 AWG 10G Gap PL 0.00 AWG 6G Assigned IDFM 0.00 AWG 6G Required IDFM 0.25 AWG 6G Gap IDFM 0.25 AWG 6G Assigned IDFT 0.25 AWG 6G Required IDFT 0.25 AWG 6G Gap IDFT 0.00 GigaDig 12G Assigned IDFM 0.00 GigaDig 12G Required IDFM 0.25 GigaDig 12G Gap IDFM 0.25 GigaDig 12G Assigned IDFT 0.20 GigaDig 12G Required IDFT 0.25 GigaDig 12G Gap IDFT 0.05 GigaDig 12G Assigned SLM-LSP 0.00 GigaDig 12G Required SLM-LSP 0.20 GigaDig 12G Gap SLM-LSP 0.20 Subtotal Assigned Subtotal Required Subtotal Gap Have read several discussions regarding use SUM(IF(FREQUENCY(NumRange,NumRange)0,1)) & SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)) functions however none seem to do what I want done. Goal is as I filter on a Project or Skill Type to have subtotals for Assigned and Required displayed at the bottom for the filtered data. -- RonB |
Hi Ron,
Yeah that is correct. In practice I think skill is column B? My transcription inserted an extra column. -- HTH RP (remove nothere from the email address if mailing direct) "RonB" wrote in message ... Thanks for the help. In order to translat this to my worksheet, I interpret the cell and range reference to C to be my Skill column with the example I provided. Is that correct? -- RonB "Bob Phillips" wrote: Ron, Try this =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="Assigned")) -- HTH RP (remove nothere from the email address if mailing direct) "RonB" wrote in message ... I trying to use the following worksheet to analysis required and assigned resources across a wide range of projects. Project Source Skill 3Q05 AWG 10G Assigned PL 0.00 AWG 10G Required PL 0.00 AWG 10G Gap PL 0.00 AWG 6G Assigned IDFM 0.00 AWG 6G Required IDFM 0.25 AWG 6G Gap IDFM 0.25 AWG 6G Assigned IDFT 0.25 AWG 6G Required IDFT 0.25 AWG 6G Gap IDFT 0.00 GigaDig 12G Assigned IDFM 0.00 GigaDig 12G Required IDFM 0.25 GigaDig 12G Gap IDFM 0.25 GigaDig 12G Assigned IDFT 0.20 GigaDig 12G Required IDFT 0.25 GigaDig 12G Gap IDFT 0.05 GigaDig 12G Assigned SLM-LSP 0.00 GigaDig 12G Required SLM-LSP 0.20 GigaDig 12G Gap SLM-LSP 0.20 Subtotal Assigned Subtotal Required Subtotal Gap Have read several discussions regarding use SUM(IF(FREQUENCY(NumRange,NumRange)0,1)) & SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)) functions however none seem to do what I want done. Goal is as I filter on a Project or Skill Type to have subtotals for Assigned and Required displayed at the bottom for the filtered data. -- RonB |
| All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com