Sumproduct a column's worth of lookups

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33
I have a text list of values that represent workers' shifts, with a lookup table that corresponds to the number of hours they work in that shift. On the second sheet, I named the ranges "Shift" and "Hours"

Sheet1:
Column A Column B Column C
Employee: Shift: Rate
Employee 1 8-12 $9
Employee 2 11-4 $12
Employee 3 12-7 $10
Hours: 16
Labor$ $166

Sheet2:
Column A Column B
Shift Hours
8-12 4
11-4 5
12-7 7

I am trying to get the total number of hours in Sheet1, of which the correct answer is 16 (4+5+7), and labor dollars in Column B of Sheet1, of which the correct answer is $166 ((4x$9)+(5x$12)+(7x$10)). I figured out the hours part: =sumproduct(sumif(shift,Sheet1!B2:B4,hours)). I still need to get the Labor$ part. I have a feeling that Sumproduct is the solution here, but I can't figure out combinations that work.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Which Excel are you working with? If it's Excel 365, then your life will be easier thanks to enhancements introduced to array formulas. You'd simply need to use:

Rich (BB code):
=SUM(VLOOKUP(shift,B2:C4,2,0)*hours)

The result:
1594917600599.png
 

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33
I do have 365, but that is not working for me for some reason (coming up as #N/A), regardless of whether or not I make it an array formula (not sure if that matters). This is also a scaled down version. I actually have 7 columns worth of shifts, one for each day of the week, and I want to sum the hours and Labor$ for each day.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
I've deleted my suggestion...I think it has some problems.
 

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33

ADVERTISEMENT

Now I get a #VALUE! error. Is that because the Shift on the first sheet is in text?
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Thanks for confirming - I suspect your Office 365 doesn't support this new functionality yet :( How about this array formula (Ctrl+Shift+Enter):

Rich (BB code):
=SUM(IF(shift=Sheet1!B2:B4,Sheet1!C2:C4,0)*hours)

If both "shift" ranges on Sheet1 and Sheet2 are of the same datatype, then there shouldn't be any issues.

1594925879506.png
 

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33

ADVERTISEMENT

Still getting a #VALUE! error...
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Do you have #VALUE! error already somewhere in your "rates" or"hours" ranges? Alternatively, could you also please check if your "rates" and "hours" ranges contain only numbers?

For example, if I put text into my "hours" range (Sheet2), I get a #VALUE! error. Make sure that your named ranges are only containing numbers.

Sheet2:
1594927607625.png


Sheet1:
1594927644302.png
 

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33
I think that's the problem. I normally reference the blank cell below the bottom of the list in case someone needs to add to the list. There will also be blanks in the employee sheet as not every employee has a shift on every day. Is there a way to get around that?

EDIT: This also only seems to work if the lists on the second sheet contain the same number of rows as the data on the first sheet. If I add another shift (i.e. a row) to either sheet, I get an #N/A error. I need to be able to add and delete rows without breaking the formula.
 
Last edited:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
Here is another approach...it's ugly, but I think it works. I added some columns at right as a double check, but they are not needed. I did not use named ranges, but those could be substituted in. I've added an extra shift definition and a couple more employees to check functionality with arrays of different sizes.
MrExcel20200716b.xlsx
ABCDEF
1EmployeeShiftRateCheck
218-129436
328-1212448
4312-710770
5411-48540
657-37856
728250
8Hours28
9Labor250
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=VLOOKUP(B2,Sheet2!$A$2:$B$5,2,0)
F2:F6F2=C2*E2
E7:F7E7=SUM(E2:E6)
C8C8=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$5,N(IF({1},MATCH($B$2:$B$6,Sheet2!$A$2:$A$5,0)) )))
C9C9=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$5,N(IF({1},MATCH($B$2:$B$6,Sheet2!$A$2:$A$5,0)) )),$C$2:$C$6)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


MrExcel20200716b.xlsx
AB
1ShiftHours
28-124
311-45
412-77
57-38
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,127,151
Messages
5,623,058
Members
415,949
Latest member
mcrandall99

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
Top