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)

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

<tbody>
</tbody>

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

Frequency	Number
Weekly	52
Monthly	12

<tbody>
</tbody>

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.