Only have formula logic in the header column while counting unique days

Question

I have a sheet with a Date and a Days heading and other non-relevant columns with more information.

Each Date only specifies the day and does not go into further precision like hours, minutes, or seconds. A day often occurs on multiple rows.

The Days column should indicate the number of unique days represented from the first entry in row 2.

I want to solve it by using an ARRAYFORMULA in the Days header (or some other logic, which is contained solely in the Days header) to fill that column, as I find it more elegant than a repeating formula in each cell.

I have tried several ways to use ARRAYFORMULA, for example, by using INDIRECT on a text representation of the Date range and then using UNIQUE and COUNTA on that. But without success.

The closest I've come without the solution breaking is getting the text representation of the relevant Date range into each cell.

I did that with the following:

={"Days";ARRAYFORMULA(("A2:A" & ROW(A2:A)))}

In the meantime, I've solved this using a repeating formula:

=IF(ISBLANK(A2),"",IFERROR(IF(A2<>A1,1,0)+B1,1))

But I'd much rather keep my "logic" in the header cell and have pure data in the table. I don't know if it is possible, though!

I am looking for an answer which either:

1. Solves it in a way that only has the logic in the Days header cell (could be using ARRAYFORMULA or some other way).
2. Explains why it's impossible.

Example sheet containing all the above: Google Sheet link

Answer 1

Use vstack(), match() and unique(), like this:

=vstack("Days", arrayformula(iferror(match(A2:A, unique(A2:A), 0))))

This formula will exactly match the desired results you show in the sample spreadsheet.

See vstack(), match() and unique().

Answer 2

Updated formula (to ignore blank cells):

={"Days";scan(0,A2:A,lambda(a,v,ifs(v="",,v<>offset(v,-1,0),a+1,1,a)))}

Answer 3

Replace the text 'Days' in B2 with the following formula:

={"Days";arrayformula(tocol($A$2:$A,1)-$A$2+1)}

Fairly self-explanatory I think, other then the TOCOL (which I'm using with its ignore argument to limit the range to be processed only to those cells which contain a date (otherwise the blank cells will result in a '1' in the 'Days' column.