Sumproduct with a lookup nested inside

WorthJ

New Member
Joined
May 4, 2018
Messages
12
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
100Monthly
20Weekly
200Monthly
60Weekly
70Weekly

<tbody>
</tbody>

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

FrequencyNumber
Weekly52
Monthly12

<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 :)
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Amount awarded (£)</td><td style=";">Frequency</td><td style="text-align: right;;"></td><td style=";">Annual Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">100</td><td style=";">Monthly</td><td style="text-align: right;;"></td><td style="text-align: right;;">11400</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">20</td><td style=";">Weekly</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">200</td><td style=";">Monthly</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">60</td><td style=";">Weekly</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">70</td><td style=";">Weekly</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet687</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUMIF(<font color="Blue">B2:B6,"Weekly",A2:A6</font>)*52+SUMIF(<font color="Blue">B2:B6,"Monthly",A2:A6</font>)*12</td></tr></tbody></table></td></tr></table><br />

Change/adjust cell references/range as needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,053
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top