Formula Help

Hello All I would like some help with a formula.

Current forluma work perfectly..

=IF(COUNTIFS(Exclude:Exclude, @cell <> 1, [Live Schedule]:[Live Schedule], @cell = 1, [Comp/ Ok To Pay]:[Comp/ Ok To Pay], @cell <> 1, [Construction Schedule]:[Construction Schedule], @cell = 1) > 0, INDEX(COLLECT([Day # of Const]:[Day # of Const], [Start Date]:[Start Date], @cell = [Days Metrics Helper Date]@row, Exclude:Exclude, @cell <> 1, [Live Schedule]:[Live Schedule], @cell = 1, [Comp/ Ok To Pay]:[Comp/ Ok To Pay], @cell <> 1, [Construction Schedule]:[Construction Schedule], @cell = 1), 1))

Now I have another column/ condition I would like to add to this…. The Column Name I added is "Ignore Day Of Const" (it is a check box column) . If that is checked it also acts like the exclude or comp/ok to pay columns where is skips to the next date… I am just not sure how to add the new column to the formula. I keep getting an error when trying to add it.

Thanks

Answers

  • Jon Barto
    Jon Barto ✭✭✭✭✭

    Nevermind. I figured it out. I forgot I had a helper column in this as well. I just also needed to add this to the helper column

    Incase anyone wants to see what I did. Adjusted the above column to this:

    =IF(COUNTIFS(Exclude:Exclude, @cell <> 1, [Live Schedule]:[Live Schedule], @cell = 1, [Comp/ Ok To Pay]:[Comp/ Ok To Pay], @cell <> 1, [Construction Schedule]:[Construction Schedule], @cell = 1, [Ignore Day Of Const]:[Ignore Day Of Const], @cell <> 1) > 0, INDEX(COLLECT([Day # of Const]:[Day # of Const], [Start Date]:[Start Date], @cell = [Days Metrics Helper Date]@row, Exclude:Exclude, @cell <> 1, [Live Schedule]:[Live Schedule], @cell = 1, [Comp/ Ok To Pay]:[Comp/ Ok To Pay], @cell <> 1, [Construction Schedule]:[Construction Schedule], @cell = 1, [Ignore Day Of Const]:[Ignore Day Of Const], @cell <> 1), 1), 1)

    Then my Helper column to this

    =MIN(COLLECT([Start Date]:[Start Date], Exclude:Exclude, @cell <> 1, [Live Schedule]:[Live Schedule], @cell = 1, [Comp/ Ok To Pay]:[Comp/ Ok To Pay], @cell <> 1, [Construction Schedule]:[Construction Schedule], @cell = 1, [Ignore Day Of Const]:[Ignore Day Of Const], @cell <> 1))

  • Georgie
    Georgie Employee

    Hey @Jon Barto,

    Thanks for posting your solution. I'm glad you got this resolved!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!