excel - Summing hours based on employee and date - Stack Overflow

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 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.

• excel
• sumifs

Share Improve this question Follow edited Feb 4 at 10:19 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Feb 4 at 10:10 TrainingTraining 111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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)