SUM values in range with same week & year in Excel

MMsmithH 0
Viewed 16

Have an excel sheet with hours | week | year in A:C and I would like to calculate "weekly hours" in column D.

enter image description here

For each unique Week & Year combination the number of hours will be displayed.

What I tried so far I set up unique combination of year&week and then tried to IF(F2=Unique,SUM(A:A),"false") but this just added entire hours and not only hours that were equal to the unique value.

Some similar but different questions & answers appear on StackOverflow. This one sums all data = monday

=SUMPRODUCT((WEEKDAY(A1:A8)=2)*B1:B8)

I couldnt really find anything where output was going to be written into each of the cells in a column as I need.

Possible answer

I think a query that sums values could work, but I am not sure. Appreciate any help.

Again a shared excel sheet on microsoft 360 is also available here.

excel-formula excel
1 Answers

Use SUMIFS():

=SUMIFS(A:A,B:B,B2,C:C,C2)

Then copy that formula down.

Or with office 365 we can do the range and it will spill:

=SUMIFS(A:A,B:B,B2:B14,C:C,C2:C14)

enter image description here

Scott Craner0
Related