1

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.

Example image of Google Sheet showing a table with Date and Days headers

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.

Example image of Google Sheet showing a table with Date and Days in an incomplete solution, where the Days simply shows the relevant range as text

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

Improve this question
0

3 Answers 3

Reset to default
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().

Improve this answer
2
  • Very nice approach.
    – Blindspots ♦
    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
0

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)))}
Improve this answer
2
  • 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
-1

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.

Improve this answer
1
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.