# sum/group days by week and year

The following is a simplified version of my problem.

I got a table in which I list whether an employee was working on that specific project or not. So the columns are calendar-week, date, day-of-week, name and the rows are the current week, the date, the day and a simple 1 if that employee was working or not.

``````A   B           C     D           F
CW  Date        Day   Employee1   Employee2
31  01.08.18    Wed   1
31  02.08.18    Thu   1           1
``````

Now I wanna assemble a second table that gives me the numbers per week:

``````A   B       C           D
CW  Year    Employee1   Employee2
31  2018    2           1
``````

I tried using `SUMIF` or `COUNTIF` but can't get it working. Here's an example for Employee1:

=COUNTIF(Table1!D:D; AND(YEAR(Table1!B:B)=Table2!B; Table1!A:A=Table2!A)

So, it should sum/count the occurrences of 1 in column D (employee 1) if the year is 2018 and the calendar week is 31. What am I doing wrong?

• Use a pivot table – Scott Craner Feb 26 '19 at 17:07
• Gosh, good idea. Got it! Nevertheless, would be interesting to know how to solve it with COUNTIF. – Matthias Feb 26 '19 at 17:10
• Use COUNTIFS and use greater than or equal to 1st of Jan less than or equal to 31st of Dec of that year. – Scott Craner Feb 26 '19 at 17:23 How it works:

• I'm assuming you have data in Range `A3:G6`.
• Fill CW `31` & `32` in `A11` and `A12`.

Note, you may use `WEEKNUM` Formula to get CW.

• Get Year in `B11` you may write this Formula: `=Year(\$B\$3)` and fill down.
• Write this Formula in `C11`, fill right then down.

`=SUMIFS(D\$3:D\$6,\$A\$3:\$A\$6,"="&ROW(A31))`

N.B.

• `ROW(A31)` in formula returns `31` which is CW to match in column `A`.
• And it becomes `32` as soon formula fills down.
• Format for DATE in Col B is `DD/MM/YY`.

Adjust cell references in the Formula as needed.