SUMPRODUCT using relational reference?

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
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>
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

Forum statistics

Threads
1,211,434
Messages
6,101,833
Members
447,758
Latest member
jojo3strada

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