Sumproduct with a lookup nested inside
Results 1 to 4 of 4

Thread: Sumproduct with a lookup nested inside

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumproduct with a lookup nested inside

    Hello,

    I've spent quite a while trying to work out how to use sum product with a look up nested inside it.

    I'm trying to calculate the annual sum of payments received by a group. People in the group receive payments either weekly or monthly.

    I receive a regular report which contains:
    • how much the payment is (Amount awarded (£))
    • how often someone is receiving payment (Frequency)



    Amount awarded (£) Frequency
    100 Monthly
    20 Weekly
    200 Monthly
    60 Weekly
    70 Weekly

    I'm trying to work out the total awarded for the whole year for the whole group.

    Frequency Number
    Weekly 52
    Monthly 12

    I've created this lookup table and I think I should be using the sumproduct formula. But I'm not sure how to nest some kind of lookup which returns an array.

    Thanks in advance for your help.

    WJ

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sumproduct with a lookup nested inside

    Hi,

    You really don't need SUMPRODUCT or a Lookup Table if there's only two variables (i.e. Weekly, Monthly):

    ABCD
    1Amount awarded (£)FrequencyAnnual Total
    2100Monthly11400
    320Weekly
    4200Monthly
    560Weekly
    670Weekly

    Sheet687



    Worksheet Formulas
    CellFormula
    D2=SUMIF(B2:B6,"Weekly",A2:A6)*52+SUMIF(B2:B6,"Monthly",A2:A6)*12



    Change/adjust cell references/range as needed.

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct with a lookup nested inside

    Great that's done it. Thanks

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sumproduct with a lookup nested inside

    You're welcome, thanks for the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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