# Sumproduct a column's worth of lookups

#### buffalofan19

##### New Member
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.

### Excel Facts

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

#### JustynaMK

##### Well-known Member
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:

#### buffalofan19

##### New Member
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
I've deleted my suggestion...I think it has some problems.

#### buffalofan19

##### New Member

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

#### JustynaMK

##### Well-known Member

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.

#### buffalofan19

##### New Member

Still getting a #VALUE! error...

#### JustynaMK

##### Well-known Member
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:

Sheet1:

#### buffalofan19

##### New Member
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
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

Replies
6
Views
395
Replies
0
Views
206
Replies
0
Views
217
Replies
1
Views
86
Replies
3
Views
29

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.

### Which adblocker are you using?

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

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