Search code examples
excelsumifs

Summing hours based on employee and date

I have a table that looks like this

Name Date Hours Total Hours (by day)
Sarah 3 Feb 3 6
John 2 Feb 2 2
Sarah 3 Feb 3 6
John 4 Feb 2 2
Sarah 2 Feb 4 4

I am entering the first three columns manually but I want to find a formula that automatically sums up the number of hours worked by each employee each day based on the date and employee name. I want everything to sit in one table and look exactly as above. I feel like this is possible and I think I have done it before but I cannot figure it out without using specific dates and specific names in the formula - is there a way to have a generic formula that automatically looks at the values in the first two columns and sums column 3 based on these values.

I know I can achieve this using

=SUMIFS(C6:C1000;A6:A1000;"Sarah";B6:B1000;"3 feb")

but I don't want specific values and rather for the formula to automatically calculate this for any employee and date.

I can do this with a pivot table but I am wondering if there is a way to do this without using a pivot table.

Solution

  • Try the following formula-

    =SUMIFS($C$2:$C$6,$A$2:$A$6,$A2,$B$2:$B$6,$B2)

    If you want aggregated result then use GROUPBY().

    =GROUPBY(A2:B6,C2:C6,SUM)