Formula help please!

I have put this formula into a text/number cell and it is working on all lines EXCEPT the lines that match "To Aim Spraypainters" and have the Install/Pickup date prior to today. I have checked that the spelling of the text matcheS the column heading and its correct.

=IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim spraypainters", [Install/Pickup date]@row < TODAY(-1))), -[Total production time]@row, 0)


Any help will be gratefully received 🙂

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Tracey Tume

    Thank you for this screen capture, this is really helpful!

    In the image I can see that your Factory Completed column has a formula as well, which is where the NO MATCH is coming form. Formula errors are like dominoes: if one of your formulas is referencing a cell with an error, it will show that same error.

    Try wrapping an IFERROR around the formula you have in the Factory Completed column:

    =IFERROR(formula, "")

    This should then resolve the issue with your other formula!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tracey Tume

    Is it not working for the dates that are specifically yesterday?

    TODAY(-1) is yesterday, and you're looking for a date prior to that, meaning it has to at least be 2 days ago. Try either adding an = sign so it also includes yesterday:

    [Install/Pickup date]@row <= TODAY(-1)

    Or look for TODAY() without a number:

    =IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim spraypainters", [Install/Pickup date]@row < TODAY())), -[Total production time]@row, 0)


    Let me know if that did the trick!

    Cheers,

    Genevieve

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Thank you Genevieve

    I had tried those options before, great minds think alike, but no luck.


    Do you think it matters if the Description column is a drop down column? I have used similar references to the Description column before in other formulas and its worked so cannot figure it out.

    Thanks for your help.

    Tracey

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Tracey Tume

    Ah, thanks for confirming.

    The dropdown column shouldn't be an issue...although it may have to do with the text that's coming through that dropdown. I'd check is to make sure you're searching for exactly what's put in the dropdown (could it be spelled slightly different of have an extra space?)

    Try opening the Column Properties to copy the text phrase, then past that exact phrase into your formula:

    =IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim Spraypainters", [Install/Pickup date]@row < TODAY(-1))), -[Total production time]@row, 0)

    Any luck? If not, it would be helpful to see a screen capture of your sheet identifying the column types - I'll re-create it on my end exactly as you have it to see what may be going on.

    Thanks!

    Genevieve

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Hi Genevieve

    I have already checked that the spelling was correct, did it exactly how you suggested.

    please see doco attached for properties of columns etc


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Tracey Tume

    Thank you for this screen capture, this is really helpful!

    In the image I can see that your Factory Completed column has a formula as well, which is where the NO MATCH is coming form. Formula errors are like dominoes: if one of your formulas is referencing a cell with an error, it will show that same error.

    Try wrapping an IFERROR around the formula you have in the Factory Completed column:

    =IFERROR(formula, "")

    This should then resolve the issue with your other formula!

    Cheers,

    Genevieve

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Morning Genevieve

    That did the trick! Wonderful, thank you so much for your help!

    Tracey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!