Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable Range w/in Sumproduct (2002)

    Within a sumproduct formula, is there any way automate a variable range of an array based on changing start and end dates in other cells?

    For instance, see the attachment for an example.

  2. #2
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Range w/in Sumproduct (2002)

    Probably a better way.....

    =SUMPRODUCT(--(A2:A21>=F6),--(A2:A21<=F7),(B2:B21*C2:C21))/SUMPRODUCT(--(A2:A21>=F6),--(A2:A21<=F7),B2:B21)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Range w/in Sumproduct (2002)

    Or, alternatively:
    In E11: =SUMPRODUCT((A2:A21>=F6)*(A2:A21<=F7)*Volume*TAT)
    In E12: =SUMPRODUCT((A2:A21>=F6)*(A2:A21<=F7)*Volume)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Range w/in Sumproduct (2002)

    =SUMPRODUCT(--(Date>=F6),--(Date<=F7),Volume,TAT)/SUMPRODUCT(--(Date>=F6),--(Date<=F7),Volume)

    Better, if it's the case that the data area is sorted in ascending order on the date column...

    G6:

    =MATCH(F6,Date)+(LOOKUP(F6,Date)<>F6)

    G7:

    =MATCH(F7,Date)

    The weighted average formula then becomes:

    =SUMPRODUCT(INDEX(Volume,G6):INDEX(Volume,G7),INDE X(TAT,G6):INDEX(TAT,G7)/SUM(INDEX(Volume,G6):INDEX(Volume,G7)))
    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Range w/in Sumproduct (2002)

    Works great, thanks all.

Posting Permissions

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