Need help with "IF" statement...

I need a field in the sheet summary to return a 2 when status2 is "complete" (otherwise 0), and I need an 8 returned when status2 and status4 are "complete".

I tried using this syntax but no joy…

=IF([Status]2=”Complete”, 2, 0) + IF(AND([Status]4=”Complete”), 8,2)

I also tried using the integrated AI but it didn't work either.

Help appreciated. Thanks

Find more posts tagged with

Accepted answers

RDRGSJ00

Just saw your latest post, glad you got it to work. 👍️

All comments

RDRGSJ00

Hello JWood,

If I understand your request, I think I came up with a solution.
I have a mock sheet with the below data and columns:

In the sheet summary, I have a field called [Status1] :

This will change from 2 to 0 to 8 depending on the values of status2 and status4. the formula is:

=IF([Status2]1 = "Complete", IF([Status4]1 = "Complete", 8, 2), 0)

This nested IF statement will first check for the value of status2, then if true, it will check the value of status4, if true, it returns an 8, if false it returns a 2, if the status2 part is false, it returns a 0.

I hope this helps.

JWood

https://community.smartsheet.com/discussion/comment/448821#Comment_448821

Thank you!

JWood

https://community.smartsheet.com/discussion/comment/448821#Comment_448821

Thank you! Unfortunately RDRGSJ00 your solution still renders an "Unparseable" in the Sheet Summary field.

My Status fields (1,2,3….) are under one column called "Status". Does this matter?

JWood

https://community.smartsheet.com/discussion/comment/448821#Comment_448821

Okay got it to work. I removed the brackets around Status2 & Status4 and the 1s as well.

RDRGSJ00

Yes, it does, the above formula is expecting individual columns. Can you tell me how you have your columns set up? I'm a little confused about how many columns you have, of what type, and how they are set up. Do you have one column of type dropdown with Status1, Status2, Status3, and Status4? and another column with "Complete", and "Incomplete" data?

RDRGSJ00

Just saw your latest post, glad you got it to work. 👍️

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.