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

#### buffalofan19

##### New Member
Okay, we're close. That formula works, so long as there is a value in each shift for the employee.

Shift Example.xlsx
ABC
1Employee:ShiftRate
2Employee 18-12\$9
3Employee 28-12\$12
4Employee 312-7\$10
5Employee 411-4\$8
6Employee 67-3\$7
7Hours:28
8Labor\$:250
Sheet1
Cell Formulas
RangeFormula
B7B7=SUMPRODUCT(INDEX(Sheet2!\$B\$2:\$B\$5,N(IF({1},MATCH(\$B\$2:\$B\$6,Sheet2!\$A\$2:\$A\$5,0)) )))
B8B8=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)
Named Ranges
NameRefers ToCells
Hours=Sheet2!\$B\$2:\$B\$5B7:B8
Shift=Sheet2!\$A\$2:\$A\$5B7:B8

If an employee is not working that day, they need to be a blank, and then the formula goes to the #N/A error.

Shift Example.xlsx
ABC
1Employee:ShiftRate
2Employee 18-12\$9
3Employee 28-12\$12
4Employee 312-7\$10
5Employee 411-4\$8
6Employee 5\$7
7Employee 67-3\$7
8Hours:#N/A
9Labor\$:#N/A
Sheet1
Cell Formulas
RangeFormula
B8B8=SUMPRODUCT(INDEX(Sheet2!\$B\$2:\$B\$6,N(IF({1},MATCH(\$B\$2:\$B\$7,Sheet2!\$A\$2:\$A\$6,0)) )))
B9B9=SUMPRODUCT(INDEX(Sheet2!\$B\$2:\$B\$6,N(IF({1},MATCH(\$B\$2:\$B\$7,Sheet2!\$A\$2:\$A\$6,0)) )),\$C\$2:\$C\$7)

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### KRice

##### Well-known Member
See if this internal error trap with IFERROR addresses the issue.
MrExcel20200716b.xlsx
ABCDEF
1EmployeeShiftRateCheck
218-129436
328-1212448
431000
5411-48540
657-37856
721180
8Hours21
9Labor180
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=IFERROR(VLOOKUP(B2,Sheet2!\$A\$2:\$B\$5,2,0),0)
F2:F6F2=C2*E2
E7:F7E7=SUM(E2:E6)
C8C8=SUMPRODUCT(IFERROR(INDEX(Sheet2!\$B\$2:\$B\$5,N(IF({1},MATCH(\$B\$2:\$B\$6,Sheet2!\$A\$2:\$A\$5,0)) )),0))
C9C9=SUMPRODUCT(IFERROR(INDEX(Sheet2!\$B\$2:\$B\$5,N(IF({1},MATCH(\$B\$2:\$B\$6,Sheet2!\$A\$2:\$A\$5,0)) )),0),\$C\$2:\$C\$6)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

#### buffalofan19

##### New Member
That worked. Thanks! Also, it appears I do not need to make it an array formula with Excel 365.

#### KRice

##### Well-known Member
That's good news...you're welcome. Yes, you're correct...Excel 365 handles array formulas automatically, without the need for special entry.

#### Amit Tandon

##### Board Regular

An easy way apparantly:

Sumif_Countif_ArrayMatch.xlsx
ABC
1
2Employee 18-12\$9
3Employee 28-12\$12
4Employee 3\$10
5Employee 411-4\$8
6Employee 67-3\$7
7
8Hours\$21
9Labor\$180
Sheet1
Cell Formulas
RangeFormula
C8C8=SUM(COUNTIF(Sheet1!B2:B6,Sheet2!A2:A5)*Sheet2!B2:B5)
C9C9=SUM(SUMIFS(C2:C6,Sheet1!B2:B6,Sheet2!A2:A5)*Sheet2!B2:B5)

#### buffalofan19

##### New Member
That works too, but for some reason, it slows down my master spreadsheet, where the SUMPRODUCT one does not.

#### Amit Tandon

##### Board Regular
That works too, but for some reason, it slows down my master spreadsheet, where the SUMPRODUCT one does not.

In case you are referring to my formulas - in my opinion, using COUNTIF / SUMIF is a more efficient method and should reduce your processing time. And each formula uses only a single COUNTIF or SUMIF with SUM.

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

1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

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