nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

If/And/Or formula help, please!

Alejandra Orihuela

Hi there,

I've been playing around with an if/and/or formula but I can't get it to work. Can someone please review my formula and tell me what I'm doing wrong?

What the formula should do is to indicate "Yes" if status of row X and row Y is any combination of "Yes" or "N/A". If there is a "No" in any either of those rows, the result of the formula should be "No". For example, row X = "Yes" and row Y = "N/A", the formula should result in a "Yes". However, something is wrong because all "Yes" and/or "N/A" combinations are yielding a "No" result :(

This is the formula I came up with =IF(AND(OR([All Required Documents Shared via Sherlock]2 = "Yes", [All Required Documents Shared via Sherlock]2 = "N/A"), [Comp Tool Attached]2 = "Yes", [Comp Tool Attached]2 = "N/A"), "Yes", "No")

Thanks in advance!!

Find more posts tagged with

Accepted answers

David Tutwiler

There are many ways to do this, but give your example, I think you're missing one more OR statement on the last check. So it would read:

=IF(AND(OR([All Required Documents Shared via Sherlock]@row = "Yes", [All Required Documents Shared via Sherlock]@row = "N/A"), OR([Comp Tool Attached]@row = "Yes", [Comp Tool Attached]@row = "N/A")), "Yes", "No")

Without that OR, it would require that the Comp Tool column would equal both Yes and N/A to solve for True. Also, I changed the row number (2) to @row.

I think you could simplify the formula if you looked for No instead of Yes/N/A, but I still think the above formula will work.

All comments

David Tutwiler

There are many ways to do this, but give your example, I think you're missing one more OR statement on the last check. So it would read:

Without that OR, it would require that the Comp Tool column would equal both Yes and N/A to solve for True. Also, I changed the row number (2) to @row.

I think you could simplify the formula if you looked for No instead of Yes/N/A, but I still think the above formula will work.

Alejandra Orihuela

Thank you, David! That worked! And you're right, the formula would be simpler if I looked at "No" only. I hadn't thought about that. I going to use this formula instead =IF(OR([All Required Documents Shared via Sherlock]@row = "No", [Comp Tool Attached]@row = "No"), "No", "Yes").

Thank you for making my day :D

David Tutwiler

Glad it worked!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.