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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
216
smozgur
smozgur
A
  • Question
Finding the appropriate function
Replies
2
Views
126
Fluff
Fluff
V
  • Question
Group list by batches based on multiple criteria
Replies
4
Views
136
hagia_sofia
hagia_sofia
C
  • Question
Picture won't paste in cell overlay
Replies
0
Views
163
Cerka
C
H
  • Question
Sumproduct based on drop down criteria
Replies
2
Views
101
Cubist
Cubist

Forum statistics

Threads
1,219,162
Messages
6,146,660
Members
450,706
Latest member
LGVBPP

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