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

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.

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().

• Very nice approach. Aug 17 at 20:18
• Thanks doubleunary! Out of the current answer this one solves it correctly, while not filling up cells where there is no date, and it is also quite readable. I added it as S3 to the sheet.
– Kent
Aug 18 at 10:56

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)))}
``````
• Thanks for the answer! It seems to work well. The only drawback would be that it continues even for blank dates. As an answer, it could use some more explanation so it would be easier to understand. But I am grateful for any answer. I added this to the sheet as S2. :)
– Kent
Aug 18 at 10:53
• Thanks for the update! I am to new to be allowed to vote up or down, so have a virtual upvote. :-)
– Kent
Aug 20 at 22:06

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.

• Thank you for your answer! At first, it looked like it worked, but it didn't when I used it with more dates with gaps. I added your suggested solution as `S1` to the linked sheet above to show what I mean. I am looking for "unique days represented," so if there are two entries for 1st Feb and then one for 1st Nov, the 1st Nov row should say Days = 2. My "repetition" solution does work like that.
– Kent
Aug 17 at 13:09