How to get the total number of days of each person per week?

I want to get the sum of days per person for every week.

I used a SUMPRODUCT formula here that gets the total number of days per week. But in my case, I need to sum up the total days of each resource per week. But it's not working.

=IF(NAME=D3,SUMPRODUCT(--(INT((DATE-(DATE(YEAR(DATE),1,1)-WEEKDAY(DATE(YEAR(DATE),1,1)-1)))/7)+1=RIGHT(G1,2)+0),$C$2:$C$1048576),0)

Please help. Thanks.

Hi

find the attachment
It may help you
I have created pivot table.

Hi kairab,

I did not try a formula. But Pivot works great too sometimes

I just added a week formula and dragged all columns to fit them into a pivot.

Try this and let me know if you are getting the expected result. Book2.xlsx


Thanks,

Bonny Tycoon



**If I was able to help please click the small star icon at the bottom left of my post **

First, rename your range DATE to DATES (DATE is a function, and you are using it), then try

=SUMPRODUCT(--(NAME=$E2),--(INT((DATES-(DATE(YEAR(DATES),1,1)-WEEKDAY(DATE(YEAR(DATES),1,1)-1)))/7)+1=--RIGHT(F$1,LEN(F$1)-FIND(" ",F$1))),DAYS)

Hiya mate,

I do it this way and just hide the column and row in yellow.

kariab.xlsx

My alternative:
Put start reference date in cell E1 (I used Jan 1, 2012) and change the heading in F1 to Week 1 (instead of Week 2) and go up from there across next columns
In cell F2: =SUMIFS($C:$C,$A:$A,$E2,$B:$B,"<="&$E$1+COLUMNS($E:E)*7)
In cell G2 (and dragged across): =SUMIFS($C:$C,$A:$A,$E2,$B:$B,"<="&$E$1+COLUMNS($E:F)*7)-SUM($F2:F2)

See attached sheet for possible answer using a week number in a Sumproduct formula.

Hi,
See attached a variant with pivot table
Put the data in a table : automaticaly update when inserting or deleting data
The weeks are obtain with the group function
Don't forget to refresh the PT when input new data
Hope this helps
Best regards