I have an excel sheet with:

  • in Col A: week numbers
  • in Col B: dates (timesheet entries)

I need to know the number of days worked for each week. So I need the number of unique date entries per week number.

I found formulas (both array as non-array) that handle this for a fixed range but I want to have the results in another column (per week number).

The result of the example dataset below would be (the colon is just for clarity):

14: 2 
15: 3 
17: 6 
20: 2 
21: 3 

If this is the source data:

14: 4/04/2012
14: 4/04/2012
15: 10/04/2012
15: 10/04/2012
15: 11/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 27/04/2012
17: 27/04/2012
20: 14/05/2012
20: 14/05/2012
21: 23/05/2012
21: 23/05/2012
21: 25/05/2012
  • Do you want a formula to pull the distinct week numbers or are you going to create the list of week numbers independently? For instance, by doing a copy and remove duplicates. – nutsch Sep 28 '12 at 22:54
  • What does it mean when the same date appears more than once? – Walter Mitty Sep 29 '12 at 18:29
  • @WalterMitty The data represents timesheet entries for a specific task/milestone. So when a week has the same date more than once, that means that there were 2 timesheet entries for that date. – Bertvan Oct 3 '12 at 12:05
  • @nutsch, no, I already have a seperate list of week numbers. – Bertvan Oct 3 '12 at 12:05
  • in that case, the countif answer below might be the easiest, unless you want the flexibility of a pivot table. – nutsch Oct 3 '12 at 16:12

to count the number of entries, use =countif(A:B,D1) assuming your week number is in cell D1, and your list of entries is in columns A and B.

Another option is to create a pivot table, with the week numbers as row labels and the count of entries as data. This will give a good summary that can be updated quickly.

| improve this answer | |

It is possible to do entirely with formulas. It needs a little indirect addressing and one (but for clarity I'll make it two) separate working columns along the original data, and three extra columns in the results table:

I'll assume, that the actual data starts in row 3 to allow for some headers. I'll use ; for argument separation, which is not default for US locale. I will not assume, that dates are sorted. With this assumption the solution would be simpler.

  1. Cell H2 (How many rows in input): =COUNT(A3:A1048576)
  2. Cell C3 (dynamic look-up range): nothing
  3. Cells C4:C1000: =ADDRESS(ROW(A$3);COLUMN(A$3)) & ":" & ADDRESS(ROW(A3);COLUMN(A3))
  4. Cell D3 (is unique): TRUE
  5. Cell D4:D1000: =COUNTIF(INDIRECT(C4);A4)=0
  6. Cell E3 (nr of unique entry): 1
  7. Cell E4:E1000: =IF(D4;E3+1;E3)
  8. Cell I2 (How many unique found): =OFFSET(E3;H2-1;0)
  9. Cell J2 (Week days range): =ADDRESS(ROW(A3);COLUMN(A3);4) & ":" & ADDRESS(ROW(A3)-1+$H$2;COLUMN(A3);4)
  10. Cell K2 (Nr of unique weekday range): =ADDRESS(ROW(E3);COLUMN(E3);4) & ":" & ADDRESS(ROW(E3)-1+$H$2;COLUMN(E3);4)
  11. Cell H5 (counter): 1
  12. Cell H6:H100 =H5+1
  13. Cell I5:I100 (position): =MATCH(H5;INDIRECT($K$2);0)
  14. Cell J5:J100 (Week day): =OFFSET($A$3;I5-1;0)
  15. Cell K5:K100 (Count): =COUNTIF(INDIRECT($J$2);J5)

The end result is in the range K5:K100.

Please note, that although I work with indirect formulas, the solution will work if you insert a column anywhere, or delete column F:F of G:G. You can also move cells, as long as you keep columns with data together.

It is important to keep everything in one worksheet. If you insist to move table H4:K100 into another worksheet, you should modify the addresses in cells J2 and K2 to include the worksheet name.

enter image description here

| improve this answer | |

A Pivot Table might be easyest


| improve this answer | |

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.