+ Reply to Thread
Results 1 to 5 of 5

Sumproduct a columns worth of lookup values

  1. #1
    Registered User
    Join Date
    06-25-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 365
    Posts
    5

    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.
    Attached Files Attached Files
    Last edited by buffalofan19; 07-16-2020 at 11:45 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    756

    Re: Sumproduct a columns worth of lookup values

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

  3. #3
    Registered User
    Join Date
    06-25-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 365
    Posts
    5

    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. #4
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    844

    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. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,520

    Re: Sumproduct a columns worth of lookup values

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help: SUMPRODUCT/LOOKUP for values
    By Jones90 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-17-2017, 04:59 PM
  2. [SOLVED] Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-29-2016, 04:11 PM
  3. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  4. [SOLVED] Help with SUMPRODUCT and Match using a Range of values for Lookup
    By Esavoye in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 01:05 PM
  5. Sumproduct with user input lookup values
    By dracoalien007 in forum Excel General
    Replies: 2
    Last Post: 05-25-2012, 09:27 AM
  6. Replies: 1
    Last Post: 06-28-2010, 09:43 PM
  7. Replies: 2
    Last Post: 04-01-2010, 11:19 AM

Bookmarks

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