SUMPRODUCT using relational reference?

S

Swiftslide

New Member
Joined
May 28, 2012
Messages
8
I have a mapping of persons to groups, in the form:

Code: 
                                                                                 <table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td class="xl65" style="height:12.75pt;width:48pt" height="17" width="64">Person</td><td class="xl65" style="width:48pt" width="64">Group</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Ben</td><td class="xl66" align="right">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Sarah</td><td align="right">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jack</td><td align="right">3</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Alice</td><td align="right">4</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jason</td><td align="right">7</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Andrew</td><td align="right">2</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jessica</td><td align="right">3</td></tr> </tbody></table>
On another sheet, I have a table of Names to hours worked, by month, in the form

Code: 
                                                                                                                                                                                                        <table border="0" cellpadding="0" cellspacing="0" width="448"><colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td class="xl67" style="height:12.75pt;width:48pt" height="17" width="64">Person</td><td class="xl67" style="width:48pt" width="64">January</td><td class="xl67" style="width:48pt" width="64">February</td><td class="xl67" style="width:48pt" width="64">March</td><td class="xl67" style="width:48pt" width="64">April</td><td class="xl67" style="width:48pt" width="64">May</td><td class="xl67" style="width:48pt" width="64">June</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Ben</td><td align="right">98</td><td align="right">56</td><td align="right">32</td><td align="right">3</td><td align="right">98</td><td align="right">32</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Sarah</td><td align="right">100</td><td align="right">8</td><td align="right">111</td><td align="right">3</td><td align="right">109</td><td align="right">99</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jack</td><td align="right">112</td><td align="right">9</td><td align="right">9</td><td align="right">12</td><td align="right">90</td><td align="right">98</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Alice</td><td align="right">47</td><td align="right">89</td><td align="right">1</td><td align="right">0</td><td align="right">83</td><td align="right">8</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jason</td><td align="right">38</td><td align="right">78</td><td align="right">33</td><td align="right">11</td><td align="right">44</td><td align="right">9</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Andrew</td><td align="right">12</td><td align="right">33</td><td align="right">130</td><td align="right">131</td><td align="right">24</td><td align="right">14</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jessica</td><td align="right">78</td><td align="right">78</td><td align="right">22</td><td align="right">77</td><td align="right">23</td><td align="right">44</td></tr> </tbody></table>
Say I want to add up the total hours of worked of all people in group 3 over the six months. If I were to add another column to the dates table, I could insert the group numbers into that column and then use the formula:

Code: 
=SUMPRODUCT(($B$2:$B$8 = 3) * $C$2:$H$8)
Alternatively, I could use the following formula, hardcoding the names of group 3 members into it:

Code: 
=SUMPRODUCT((($B$2:$B$8 = "Jack") + ($B$2:$B$8 = "Jessica")) * $C$2:$H$8)
However, I would rather use relational references. I.e. I want a formula that looks up the first table to find which Names are in group 3, then performs the sumproduct (or some equivalent function to get the total hours). Is this possible?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Sorry, shouldn't have used code tags. Tables are as follows:

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td class="xl65" style="height:12.75pt;width:48pt" height="17" width="64">Person</td><td class="xl65" style="width:48pt" width="64">Group</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Ben</td><td class="xl66" align="right">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Sarah</td><td align="right">1</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jack</td><td align="right">3</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Alice</td><td align="right">4</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jason</td><td align="right">7</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Andrew</td><td align="right">2</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt" height="17">Jessica</td><td align="right">3</td></tr></tbody></table>

<table border="0" cellpadding="0" cellspacing="0" width="448"><colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"><td class="xl67" style="height:12.75pt;width:48pt" height="17" width="64">Person</td><td class="xl67" style="width:48pt" width="64">January</td><td class="xl67" style="width:48pt" width="64">February</td><td class="xl67" style="width:48pt" width="64">March</td><td class="xl67" style="width:48pt" width="64">April</td><td class="xl67" style="width:48pt" width="64">May</td><td class="xl67" style="width:48pt" width="64">June</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Ben</td><td align="right">98</td><td align="right">56</td><td align="right">32</td><td align="right">3</td><td align="right">98</td><td align="right">32</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Sarah</td><td align="right">100</td><td align="right">8</td><td align="right">111</td><td align="right">3</td><td align="right">109</td><td align="right">99</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jack</td><td align="right">112</td><td align="right">9</td><td align="right">9</td><td align="right">12</td><td align="right">90</td><td align="right">98</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Alice</td><td align="right">47</td><td align="right">89</td><td align="right">1</td><td align="right">0</td><td align="right">83</td><td align="right">8</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jason</td><td align="right">38</td><td align="right">78</td><td align="right">33</td><td align="right">11</td><td align="right">44</td><td align="right">9</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Andrew</td><td align="right">12</td><td align="right">33</td><td align="right">130</td><td align="right">131</td><td align="right">24</td><td align="right">14</td></tr><tr style="height:12.75pt" height="17"><td class="xl68" style="height:12.75pt" height="17">Jessica</td><td align="right">78</td><td align="right">78</td><td align="right">22</td><td align="right">77</td><td align="right">23</td><td align="right">44</td></tr></tbody></table>
 
Upvote 0
Swiftslide said:
Sorry, shouldn't have used code tags. Tables are as follows:

<TABLE cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>Person</TD><TD class=xl65 style="WIDTH: 48pt" width=64>Group</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Ben</TD><TD class=xl66 align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Sarah</TD><TD align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Jack</TD><TD align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Alice</TD><TD align=right>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Jason</TD><TD align=right>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Andrew</TD><TD align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="HEIGHT: 12.75pt" height=17>Jessica</TD><TD align=right>3</TD></TR></TBODY></TABLE>

<TABLE cellSpacing=0 cellPadding=0 width=448 border=0><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>Person</TD><TD class=xl67 style="WIDTH: 48pt" width=64>January</TD><TD class=xl67 style="WIDTH: 48pt" width=64>February</TD><TD class=xl67 style="WIDTH: 48pt" width=64>March</TD><TD class=xl67 style="WIDTH: 48pt" width=64>April</TD><TD class=xl67 style="WIDTH: 48pt" width=64>May</TD><TD class=xl67 style="WIDTH: 48pt" width=64>June</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Ben</TD><TD align=right>98</TD><TD align=right>56</TD><TD align=right>32</TD><TD align=right>3</TD><TD align=right>98</TD><TD align=right>32</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Sarah</TD><TD align=right>100</TD><TD align=right>8</TD><TD align=right>111</TD><TD align=right>3</TD><TD align=right>109</TD><TD align=right>99</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Jack</TD><TD align=right>112</TD><TD align=right>9</TD><TD align=right>9</TD><TD align=right>12</TD><TD align=right>90</TD><TD align=right>98</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Alice</TD><TD align=right>47</TD><TD align=right>89</TD><TD align=right>1</TD><TD align=right>0</TD><TD align=right>83</TD><TD align=right>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Jason</TD><TD align=right>38</TD><TD align=right>78</TD><TD align=right>33</TD><TD align=right>11</TD><TD align=right>44</TD><TD align=right>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Andrew</TD><TD align=right>12</TD><TD align=right>33</TD><TD align=right>130</TD><TD align=right>131</TD><TD align=right>24</TD><TD align=right>14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="HEIGHT: 12.75pt" height=17>Jessica</TD><TD align=right>78</TD><TD align=right>78</TD><TD align=right>22</TD><TD align=right>77</TD><TD align=right>23</TD><TD align=right>44</TD></TR></TBODY></TABLE>
Click to expand...
Try this...

Book1
ABCDEFG
1PersonGroup_GroupTotal__
2Ben1_3652__
3Sarah1_____
4Jack3_____
5Alice4_____
6Jason7_____
7Andrew2_____
8Jessica3_____
9_______
10_______
11PersonJanFebMarAprMayJun
12Ben98563239832
13Sarah1008111310999
14Jack11299129098
15Alice478910838
16Jason38783311449
17Andrew12331301312414
18Jessica787822772344
Sheet1

This array formula** entered in E2:

=SUM(IF(ISNUMBER(MATCH(A12:A18,A2:A8,0)),IF(B2:B8=D2,B12:G18)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Excel Workbook
ABCDEFG
1PersonGroupGroupTotal
2Ben13652
3Sarah1
4Jack3
5Alice4
6Jason7
7Andrew2
8Jessica3
9
10
11PersonJanFebMarAprMayJun
12Ben98563239832
13Sarah1008111310999
14Jack11299129098
15Alice478910838
16Jason38783311449
17Andrew12331301312414
18Jessica787822772344
Sheet1


Normally enter
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
A
  • Question Question
Finding the appropriate function
Replies
2
Views
232
Fluff
Fluff
A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
212
DanteAmor
DanteAmor
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
142
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top