Sumproduct with a lookup nested inside

[WorthJ]

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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Amount awarded (£)[/TD]
[TD]Frequency[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Monthly[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Weekly[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Monthly[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Weekly[/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD]Weekly[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 250"]
<tbody>[TR]
[TD]Frequency[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Weekly[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Monthly[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[jtakw]

Hi,

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

Book1
	A	B	C	D
1	Amount awarded (£)	Frequency		Annual Total
2	100	Monthly		11400
3	20	Weekly
4	200	Monthly
5	60	Weekly
6	70	Weekly
Sheet687

Cell Formulas
Range	Formula
D2	=SUMIF(B2:B6,"Weekly",A2:A6)*52+SUMIF(B2:B6,"Monthly",A2:A6)*12

Change/adjust cell references/range as needed.

 

[WorthJ]

Great that's done it. Thanks

 

[jtakw]

You're welcome, thanks for the feedback.