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
  • 1
    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

enter image description here

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.



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

| improve this answer | |
  • Glad to help you @Matthias ,,, keep asking ☺ – Rajesh S Mar 1 '19 at 4:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.