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.
- Cell H2 (How many rows in input):
=COUNT(A3:A1048576)
- Cell C3 (dynamic look-up range): nothing
- Cells C4:C1000:
=ADDRESS(ROW(A$3);COLUMN(A$3)) & ":" & ADDRESS(ROW(A3);COLUMN(A3))
- Cell D3 (is unique):
TRUE
- Cell D4:D1000:
=COUNTIF(INDIRECT(C4);A4)=0
- Cell E3 (nr of unique entry):
1
- Cell E4:E1000:
=IF(D4;E3+1;E3)
- Cell I2 (How many unique found):
=OFFSET(E3;H2-1;0)
- Cell J2 (Week days range):
=ADDRESS(ROW(A3);COLUMN(A3);4) & ":" & ADDRESS(ROW(A3)-1+$H$2;COLUMN(A3);4)
- Cell K2 (Nr of unique weekday range):
=ADDRESS(ROW(E3);COLUMN(E3);4) & ":" & ADDRESS(ROW(E3)-1+$H$2;COLUMN(E3);4)
- Cell H5 (counter):
1
- Cell H6:H100
=H5+1
- Cell I5:I100 (position):
=MATCH(H5;INDIRECT($K$2);0)
- Cell J5:J100 (Week day):
=OFFSET($A$3;I5-1;0)
- 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.
