# Sumproduct a columns worth of lookup values

1. ## Sumproduct a columns worth of lookup values

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 have a feeling that Sumproduct is the solution here, but I can't figure out combinations that work.

EDIT: I figured out the hours part: =sumproduct(sumif(shift,Sheet1!B2:B4,hours)). I still need to get the Labor\$ part.

2. ## Re: Sumproduct a columns worth of lookup values

B6=SUMPRODUCT(Sheet2!B2:B4,Sheet1!C2:C4)

3. ## Re: Sumproduct a columns worth of lookup values

That only works if every shift is taken. I need it to look up the shift, and then multiply it by the hours.

4. ## Re: Sumproduct a columns worth of lookup values

Since you're using M365, will this work for you in B6?
=SUMPRODUCT(XLOOKUP(B2:B4,Shift,Hours,,0),C2:C4)

5. ## Re: Sumproduct a columns worth of lookup values

=SUMPRODUCT(SUMIF(Shift,B2:B4,Hours)*C2:C4)

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1