Stumped on combining AND/IF/OR formula

I need a formula where if Innovate Audit is Yes, or Innovate Audit Upgrade is Yes, but Pull Print is No, then the the # of devices requiring in printer agent/40 +1


Here is what I came up with so far...


=IF(OR([Innovate Audit]@row = "Yes", [Innovate Audit Included in Upgrade?]@row = "Yes"), IF(AND([HP Advance Pull Print?]@row = "No"), ([Devices Requiring In- Printer Agent]@row/40+1) ,0)

Tags:

Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi @Stacy Stoffel

    Smartsheet's information on #UNPARSEABLE suggests we " Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ")."

    May I suggest selecting each of the square bracket references and replacing the reference by clicking in the column that relates to the data. This will ensure the correct column is referenced.

    In the pic provided I'm not seeing any of the column names in the formula (e.g. [HP Advance Pull Print?]). Could it be that the column name doesn't have a question mark, or has a space after it?

    You'll know when it's referencing data when each square bracket reference changes colour, like this:

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Stacy,

    It looks like your formula works (refer screenshot below).

    I normally combine these as an IF(AND(... , OR(... , ...)), 1 , 0) formula (also below, which also works)

    =IF(AND([HP Advance Pull Print?]@row = "No", OR([Innovate Audit]@row = "Yes", [Innovate Audit Included in Upgrade?]@row = "Yes")), ([Devices Requiring In- Printer Agent]@row / 40 + 1), 0)

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Stacy Stoffel
    Stacy Stoffel ✭✭✭✭✭

    When I use my formula and yours, i get #UNPARSEABLE :(


  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi @Stacy Stoffel

    Smartsheet's information on #UNPARSEABLE suggests we " Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ")."

    May I suggest selecting each of the square bracket references and replacing the reference by clicking in the column that relates to the data. This will ensure the correct column is referenced.

    In the pic provided I'm not seeing any of the column names in the formula (e.g. [HP Advance Pull Print?]). Could it be that the column name doesn't have a question mark, or has a space after it?

    You'll know when it's referencing data when each square bracket reference changes colour, like this:

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Stacy Stoffel
    Stacy Stoffel ✭✭✭✭✭

    For some reason I couldn't get the cells to highlight on the row I was working on the formula with. I went down a row, added the formula and then made it a column formula and that fixed my issue and the formula now is working great! Thanks for all your help!

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Great news.

    Glad to be of help.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!