+ Reply to Thread
Results 1 to 5 of 5

Sum Function with Xlookup

  1. #1
    Registered User
    Join Date
    05-26-2016
    Location
    Silverdale, WA
    MS-Off Ver
    2013
    Posts
    3

    Sum Function with Xlookup

    I have a formula that I can't get quite right, and I'm hoping to get insight on. I'm pulling information for one week at a time, and using the xlookup function for the formula but need to auto calculate the sum of Actual Hours per day. I think I have most of the formula correct, except I can't quite figure out where the sum formula would go into place for Column F.

    Here is the formula I have:
    =XLOOKUP($I7,$A:$A,$F:$F,MATCH($L$1,$G:$G,0)*MATCH(J7,$C:$C))

    I'm at a total loss, so any suggestions would be Amazing!

    Employee Doc PR.pdf

  2. #2
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Sum Function with Xlookup

    where do you want to use the formual/fuction to get what result from what data source based on what criteria ?????????????????????????????????????

    Without detailed information setup in "name" , nobody will know what "xlookup" means

  3. #3
    Registered User
    Join Date
    05-26-2016
    Location
    Silverdale, WA
    MS-Off Ver
    2013
    Posts
    3

    Re: Sum Function with Xlookup

    The end result I want is to get the sum of F. That is depending on some factors. Employee name in Column I matches with employee name in A, dates are the exact same in J and C, and Column G matches with K1-Q1

  4. #4
    Registered User
    Join Date
    05-26-2016
    Location
    Silverdale, WA
    MS-Off Ver
    2013
    Posts
    3

    Re: Sum Function with Xlookup

    The answers I want, and the formula in question, are all located in the table K2-Q29

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sum Function with Xlookup

    Quote Originally Posted by sawaccouniting View Post
    . . . I can't quite figure out where the sum formula would go into place for Column F. . . .

    =XLOOKUP($I7,$A:$A,$F:$F,MATCH($L$1,$G:$G,0)*MATCH(J7,$C:$C))

    . . .
    XLOOKUP's 4th argument is what you want returned when there's no match. Do you actually want the result of MATCH($L$1,$G:$G,0)*MATCH(J7,$C:$C)
    returned when there's no match? Note that the col G match is exact while the col C match is interval-based and assumes C:C is sorted in ascending
    order, and MATCH(..)*MATCH(..) is the product of row indices, which wouldn't seem to be a useful value. Note also that your I7 lookup against A:A
    is also interval-based and assumes A:A is sorted in ascending order.

    If what you want is a sum from K2:Q29, I'm reasonably certain the formula will look more like

    =SUM(INDEX(K2:Q2,XMATCH(..)):INDEX(K2:K29,XMATCH(..)))

    That said, your attachment isn't useful. You need to show A1:Q29 at least, and I'm not going to retype anything from a PDF anyway. Post a workbook
    with sample data, though you should change any actual names with made-up tokens, easiest to replace the 1st name with A, the 2nd name with B, etc.

+ 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. XLOOKUP availability?
    By PeteABC123 in forum Excel General
    Replies: 8
    Last Post: 10-22-2020, 06:10 PM
  2. Replies: 14
    Last Post: 09-28-2019, 10:32 PM
  3. COUNTUNIQUE Function in Google Sheets; Excel lacks a direct counterpart to this function?
    By PivotTablePSHomage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2018, 05:27 AM
  4. [SOLVED] Usage of standard functions into custom function (or user defined function)
    By tusharb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2016, 12:43 AM
  5. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  6. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  7. Replies: 1
    Last Post: 03-21-2012, 11:22 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