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.
 

buffalofan19

New Member
Joined
Jun 26, 2009
Messages
33
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)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 26, 2009
Messages
33
That worked. Thanks! Also, it appears I do not need to make it an array formula with Excel 365.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 26, 2009
Messages
33
That works too, but for some reason, it slows down my master spreadsheet, where the SUMPRODUCT one does not.
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

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