# Sum Function with Xlookup

1. ## 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. ## 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. ## 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. ## Re: Sum Function with Xlookup

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

5. ## Re: Sum Function with Xlookup

Originally Posted by sawaccouniting
. . . 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.

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