Getting #INVALID DATA TYPE using IF(AND and IF(OR together

=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1, "Green"), IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red"))

Formula above. Would like to display green ball when all checked, Yellow when some checked, Red if none checked. Can't figure out the error - any help appreciated. Columns are all spelled correctly.

Found the formula here: https://community.smartsheet.com/discussion/7293/automate-ryg-ball-based-on-checkboxes-in-other-columns

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

You just have a misplaced parenthesis is all. Give this a try (bold section is the adjustment.

=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Green", IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red"))

All comments

Paul Newcome

You just have a misplaced parenthesis is all. Give this a try (bold section is the adjustment.

=IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Green", IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red"))

BTeeter

Ahhhhh geez. Thank you! All is right again :)

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response as "helpful" so that others searching for a similar solution can know that one may be found here.

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.