Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
    
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.

share|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

share|improve this answer

A Pivot Table might be easyest

Example

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .