+ Reply to Thread
Results 1 to 4 of 4

Subtotal function with Filtered Data

  1. #1
    RonB
    Guest

    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

  2. #2
    Bob Phillips
    Guest

    Re: Subtotal function with Filtered Data

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    RonB
    Guest

    Re: Subtotal function with Filtered Data

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Subtotal function with Filtered Data

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1