Count cell between dates by user

[DalPai]

Hello all,

I have a tricky problem that I can't solve, and I would like help from more experienced people.

I have a gantt chart to show the progress of tasks by user.
Bar colors are made by conditional formatting, but each cell that is painted also has a value of "1" (while unpainted cells have a value of "0").

I am trying to create a matrix where I can show how many times the number "1" has appeared to the user within a time frame.

For example, in the column 28/10 (which represents the entire week of October 28), Charles should have the number 15.

Attached the example used
https://drive.google.com/file/d/1xQ8uJCIg5ofVQzC0c6c9JOiLhmJ5v6FT/view?usp=sharing

Best Regards,

 

[RasGhul]

Hi Dal,

in D6 paste & Copy across;

=SUMPRODUCT(('ACTIVITY SCHEDULE'!$K$5:$JZ$5>='RESOURCE MATRIX'!D$5)*('ACTIVITY SCHEDULE'!$K$5:$JZ$5<='RESOURCE MATRIX'!D$5+4)*('ACTIVITY SCHEDULE'!$E$6:$E$11='RESOURCE MATRIX'!$C6)*'ACTIVITY SCHEDULE'!$K$6:$JZ$11)

Note that Charles only has 15 if his name is in E6 as well.

 

[DalPai]

Thnks @RasGhul, worked like a charm