Count days from TODAY() until text in adjacent cell changes

[Enounce]

Hi all,

I'm making a travel plan, where I would like my sheet to calculate when the next destination changes from TODAY(). I've attached a picture and sheet which exemplify the issue at hand.

FrJXxwu.png

Thanks in advance.

[AliGW]

Will this work for you?

=IF(COUNTIF(B$2:B2,B2)=1,COUNTIF($B$2:$B$20,B2),"")

You may need this if your locale is a European one:

=IF(COUNTIF(B$2:B2;B2)=1;COUNTIF($B$2:$B$20;B2);"")

[Enounce]

Hi AliGW,

Thanks for the feedback!

The formula goes in a status field that will need to update depending on today(), lookup the destination, find when the destination updates, and then calculate the number of days between the two destinations. Edit: Just tried adding a photo to explain a bit better. So for instance, tomorrow the field would need to show "3", and so forth.

EDucvsM.png

I'm not sure if that made it any clearer, otherwise, let me know.

[josephteh]

Formula =COUNTIF(INDIRECT("B"&MATCH(TODAY(),C:C,0)&":B10000"),INDEX(B:B,MATCH(TODAY(),C:C,0)))
You may need to replace comma with semicolon for Europe

[Fluff13]

Another option

Please Login or Register  to view this content.

[Enounce]

Formula =COUNTIF(INDIRECT("B"&MATCH(TODAY(),C:C,0)&":B10000"),INDEX(B:B,MATCH(TODAY(),C:C,0)))
You may need to replace comma with semicolon for Europe

I actually thought this one worked but by coincidence, I typed in the same destination a bit further down which made the count increase, which it ideally shouldn't. It has to "break" whenever a value interrupts the streak.

rao3tNz.png

Another option

Please Login or Register  to view this content.

Unfortunately, same as above. I also suspect Xmatch will cause issues for my travel buddy. He's on an older version.

Thanks for all the suggestions, and sorry for not realizing that destinations could appear multiple times.

[AliGW]

I typed in the same destination a bit further down which made the count increase, which it ideally shouldn't. It has to "break" whenever a value interrupts the streak.

Where in your opening post did you state this as a requirement?

Provide a larger set of sample data with results mocked up by hand. Make sure it covers all eventualities.

[Fluff13]

Ok, how about

Please Login or Register  to view this content.

[Enounce]

Where in your opening post did you state this as a requirement? Provide a larger set of sample data with results mocked up by hand. Make sure it covers all eventualities.

I agree with you completely, I simply hadn't thought of it at the time of asking, hence my apology.

(...) sorry for not realizing that destinations could appear multiple times.

I really do try to provide all feedback and illustrate through sample data and images. I'll try to do even better next time.

Ok, how about

Please Login or Register  to view this content.

This one works flawlessly. Thanks so much, Fluff13!

My buddy uploaded the sheet to a Google Drive, but apparently, the AGGREGATE function isn't available there. Am I allowed to ask for a Google-proof formula as well here, or should I open a new topic in the other platforms section?

[Fluff13]

Glad to help & thanks for the feedback.

I don't know much about Google, so cannot help with that.