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)

Find more posts tagged with

Formulas

Accepted answers

Jason Albrecht

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?

All comments

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?

Stacy Stoffel

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

Jason Albrecht

Hi @Stacy Stoffel

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?

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

Great news.

Glad to be of help.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.