Excel – How to Sum Values Based on XLOOKUP Formula

excelexcel-formulasumifsxlookup

The table below shows a list of employees and number of hours they have worked in that week. I am trying to come up with formula that would return the total number of hours worked per employee per month (sum the weekly numbers) to fill in the lower table. I have tried combining the XLOOKUP and SUMIFS formula but so far no luck. Does anyone have any tips by chance?

enter image description here

Resource Name Resource Level 7/22/2024 7/29/2024 8/5/2024 8/12/2024 8/19/2024 8/26/2024 9/2/2024 9/9/2024
Rob Manager 40 20 37 29 23 31 33 32
Tom Analyst 30 25 26 27 19 39 19 31
Jessica Senior Analyst 20 34 30 35 34 30 29 24
Julia Business Analyst 15 34 28 22 27 36 38 19
Resource Name Resource Level July August September
Rob Manager
Tom Analyst
Jessica Senior Analyst
Julia Business Analyst

Best Answer

Instead of using XLOOKUP() use either SUM() or SUMPRODUCT(). The following is the simplest of all and remember a SUM() function will be always efficient and quicker, there is no match to it.

When you have output data laid out where you have the resource name and resource level on the left, and the headers with the formatting as mmm-e you can simply use the SUM() function performing a Boolean Logical Operation, no fancy formulas or functions are required. It is way easy to understand and easy to debug, moreover it will be very efficient as well. Just try !

Otherwise if you are attempting for second dynamic array formula then use the Option Two which I have proposed.

enter image description here

    =SUM((TEXT($C$1:$J$1,"mmm-e")=TEXT(C$8,"mmm-e"))*
     ($A9=$A$2:$A$5)*($B9=$B$2:$B$5)*
     $C$2:$J$5)

OPTION TWO:

Instead of using multiple LAMBDA() iterations using functions like REDUCE() or Custom LAMBDA() with a combination of another LAMBDA() helper like MAP() you can easily transform your data into one single dynamic array using MMULT() :

enter image description here

=LET(
     _Data, Table1[#All],
     _Rname, DROP(TAKE(_Data,,1),1),
     _Rlevel, DROP(INDEX(_Data,,2),1),
     _Headers, TEXT(DROP(TAKE(_Data,1),,2),"mmm-e"),
     _Uniq, UNIQUE(_Headers,1),
     _Body, DROP(_Data,1,2),
     _Output, HSTACK(_Rname,_Rlevel,MMULT(_Body,N(_Uniq=TOCOL(_Headers)))),
     VSTACK(HSTACK(TAKE(_Data,1,2),_Uniq),_Output))

Please ensure to convert your range into Structured References aka Tables. There are many reasons to use this feature of excel. Two such reasons are it makes easier to read the formulas as well as it will auto resize the formula when there is a change in dimensions of the source data.

To explain more clearly please read:

  • _Data Variable refers to the entire table Table1[#All]
  • Rname Variable extracts the first columns from the source excluding the headers using TAKE() and DROP() --> TAKE() helps in taking the first column while DROP() involves in dropping the headers.
  • _Rlevel Variable Similarly extracts the second column from the source likewise above using the same methodology by changing the column_index in the INDEX()
  • _Headers Variable extracts the first row using TAKE() function while DROP() helps in removing first two columns, and lastly using TEXT() function which formats the dates as a three-letter abbreviation of the month followed by a dash/hyphen and the year.The formatting notation for the full name of the month is mmmm while for only Jul or Apr etc it would be like mmm which already been used, e refers to the year which is universal.
  • _Uniq Variable refers to unique list of headers.
  • _Body Variable is the values areas, extracted using DROP() function.
  • _Output Variable which combines the arrays of _Rname and Rlevel with the matrix multiplication of _Body and a Binary array created between the _Uniqlist and the transposed_Headers, enclosed within HSTACK()` to return one single output.
  • Lastly, using VSTACK() to give some better look we are placing the headers!.
Related Question