google-sheetssumgoogle-sheets-formulayearmonth

sum worked hours per month in multiple years google sheet


I have a sheet where each day the working hours are noted for every employee in top row, are dates .

i want to sum worked hours every month of a year between 2 dates and get an array/employee

i want to get an array that sums the worked hours for sept 23 and oct 23 for an employee, dynamically

{sum (sept23 worked hrs);sum(oct23 worked hrs);...}

I am trying to work with this formula, in a smaller data area, b1:d3 around but I'm missing something

=ARRAYFORMULA(IFNA(VLOOKUP(MONTH(B1:D1&1), QUERY(B1:D3, "select month(row1),sum(row2) group by month(row1)"), 2, false)))

test page


Solution

  • Here's one approach you may test out:

    =let(Λ,unique(filter(text(B1:1,"mmm-yy"),B1:1<>""),1), 
         vstack(hstack(,Λ),hstack(A2:A3,byrow(B2:3,lambda(Σ,index(sumif(text(B1:1,"mmm-yy"),Λ,Σ)))))))
    

    enter image description here