# 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