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):
- 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):
- Cell D4:D1000:
- Cell E3 (nr of unique entry):
- Cell E4:E1000:
- Cell I2 (How many unique found):
- 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):
- Cell H6:H100
- Cell I5:I100 (position):
- Cell J5:J100 (Week day):
- Cell K5:K100 (Count):
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.