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:
[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![Smile :-) :-)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
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
![Smile :-) :-)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)