# Excel: count number of unique/distinct row in range with condition

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.

-

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.

-

A Pivot Table might be easyest

-