Xlookup with dates and sum formula in EXCEL

Question

I am trying to figure out a formula to find the sum of hours worked for each worker per month and also per project. I am pretty sure the XLOOKUP, AND/IF, and SUBTotal could be useful but I am not exactly sure how to combine it all. Here are the exact data.

Lookup values for each employee (Column A), Lookup values for each project per employee (Column B), Lookup values for each Timesheet period (column C) format: 2022/06/03, Then sum the total hours work of each employee, per project per month (Column D)

Thanks in advance!

I tried the XLOOKUP formula AND formula for the dates SUBTOTAL formula for the hours.

Answer 1

This approach uses O365 functions to generate an array solution. The reference dates are generated in cell H2 as a 1x3 array and formatted as date as follows: mmm-yy (not relevant for the calculation, just for visualization):

=EDATE(DATE(2022,6,1),SEQUENCE(1,3,0))

then in H3 put the following formula and expand it to the right, no need to expand it down:

=LET(set, $A$2:$D$13, lkup, $F$3:$G$8, SOM, H2, projects, INDEX(set,,1), 
  employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4),
  MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj,empl, LET(EOM, EOMONTH(SOM,0),
    SUM(FILTER(hours, (projects =prj) * (employes = empl) * (timesheets >= SOM) 
     * (timesheets <= EOM),0))
  )))
)

where LET is used for easy reading and composition.

Note: Instead of SUM/FILTER you can use one of the following options too:

SUMPRODUCT(hours, (projects =prj) * (employes = empl) * (timesheets >= SOM) 
 * (timesheets <= EOM))

or

SUMPRODUCT(hours, N(projects =prj), N(employes = empl), N(timesheets >= SOM), 
  N(timesheets <= EOM))

or just SUM:

SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) 
  * (timesheets <= EOM))

it is a matter of preference.

Here is the output:

If you wonder if it is possible to avoid expanding to the right the formula, so we can have the entire result in just one formula, yes it is possible.

Alternative 1: Using DROP/REDUCE/HSTACK:

=LET(set, A2:D13, lkup, F3:G8, SOMs, H2:J2, projects, INDEX(set,,1), 
  employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4),
  DROP(REDUCE(0,SOMs, LAMBDA(acc, SOM, HSTACK(acc, LET(EOM, EOMONTH(SOM,0),
  MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj, empl,
   SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) 
    * (timesheets <= EOM))
  )))))),,1)
)

Alternative 2: Using TEXTSPLIT/TEXTJOIN:

=LET(set, A2:D13, lkup, F3:G8, SOMs, H2:J2, projects, INDEX(set,,1), 
  employes, INDEX(set,,2),timesheets, INDEX(set,,3), hours, INDEX(set,,4),
  byC, BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0), 
  mp, MAP(INDEX(lkup,,1), INDEX(lkup,,2), LAMBDA(prj, empl,
     SUM(hours * (projects =prj) * (employes = empl) * (timesheets >= SOM) 
      * (timesheets <= EOM))
  )), TEXTJOIN(",",, mp)
  ))),
  TRANSPOSE(1*TEXTSPLIT(TEXTJOIN(";",, byC), ",", ";"))
)

Note: Under both Alternatives there is no need to use $-notation in the ranges.

Answer 2

Perhaps you could try using SUMPRODUCT() as well

• Formula used in cell C20

=SUMPRODUCT((A20=$A$2:$A$13)*(B20=$B$2:$B$13)*(TEXT($C$2:$C$13,"mm/yyyy")="06/2022")*($D$2:$D$13))

---

Note: Use cell reference like as below to make it dynamic

• Formula used in cell C20

=SUMPRODUCT(($A20=$A$2:$A$13)*($B20=$B$2:$B$13)*(TEXT($C$2:$C$13,"mm/yyyy")=TEXT(C$18,"mm/yyyy"))*($D$2:$D$13))

Answer 3

Sumifs solves your problem

=SUMIFS($D$2:$D$12,$A$2:$A$12,$A15,$B$2:$B$12,$B15,$C$2:$C$12,">="&DATE(2022,7,1);$C$2:$C$12,"<="&DATE(2022,7,31))