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.
CodePudding user response：
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))
CodePudding user response：
Perhaps you could try using 
• 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))
CodePudding user response：
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:
