# Thread: Sumproduct with a lookup nested inside Thanks:  1 Post #5309730 (1) Likes:  1 Post #5309730 (1)

1. ## 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.

WJ

2. ## 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

3. ## Re: Sumproduct with a lookup nested inside

Great that's done it. Thanks

4. ## Re: Sumproduct with a lookup nested inside

You're welcome, thanks for the feedback.