Multiple IFs help

Folks, if I use the below formulas individually they work fine:

=IF(AND([Due Date]69 >= "01/01/18", [Due Date]69 <= "01/31/18"), "January", "-")

=IF(AND([Due Date]69 >= "02/01/18", [Due Date]69 <= "02/28/18"), "February", "-")

I have tried to put them together like this:

=IF(AND([Due Date]69 >= "01/01/18", [Due Date]69 <= "01/31/18"), "January", "-", IF(AND([Due Date]69 >= "02/01/18", [Due Date]69 <= "02/28/18"), "February", "-")

and many other variations and cannot get it to work.

The whole idea is to identify which month the "Due Date" is, if the date entered is: 01/015/18 the result should be "January" or if the "Due Date" is changed to 02/20/18 it would display " February, if 03/10/18 is entered the result would be "-"

For simplicity I have only put two months but it is meant to be for the entire year.

Regards!

Sumeluar

Find more posts tagged with

Accepted answers

Paul Newcome

When nesting IF functions, you place the next IF statement in the [value if false] position (where you have your "-").

=IF(AND([Due Date]69 >= "01/01/18", [Due Date]69 <= "01/31/18"), "January", IF(AND([Due Date]69 >= "02/01/18", [Due Date]69 <= "02/28/18"), "February", "-"))

Genevieve P.

Hi @Kiley Vander Wyst

The statements say:

If the value in this cell is greater than 2, "Green"
otherwise, if the value is exactly equal to 1, "Yellow"

Any other value will be Red, which includes anything less than 1 or from 1.1 - 1.9

Can you state what it is you want to do? Then we can adjust the formula instructions. It sounds like perhaps you want all values between 1 - 2 as "Yellow", and if it's 2 it should be "Green", is that correct?

Try:

=IF([12/4/23-12/8/23]@row >= 2, "Green", IF([12/4/23-12/8/23]@row >= 1, "Yellow", "Red"))

Cheers,

Genevieve

All comments

Mike Wilday

I think your issue might be in that you've combined the IF statements, but haven't nested them. Each IF statement should be followed by the next IF statement instead of the else... you have "-" as your else statement and its smattered in and among your formula. Your "-" should go at the end of the entire statement and then you have to close each IF statement at the end which is why there are 2 closing parenthesis. Try it out and let me know if that works.

=IF(AND([Due Date]69 >= "01/01/18", [Due Date]69 <= "01/31/18"), "January", IF(AND([Due Date]69 >= "02/01/18", [Due Date]69 <= "02/28/18"), "February", "-"))

Paul Newcome

When nesting IF functions, you place the next IF statement in the [value if false] position (where you have your "-").

=IF(AND([Due Date]69 >= "01/01/18", [Due Date]69 <= "01/31/18"), "January", IF(AND([Due Date]69 >= "02/01/18", [Due Date]69 <= "02/28/18"), "February", "-"))

Mike Wilday

So concisely stated.

Paul Newcome

I didn't see your comment before mine. Haha. I had opened it to answer earlier in the morning, but I got crazy busy for a while and didn't think to refresh before typing my reply.

Sumeluar

Thanks a bunch guys, now I know how to do it.

Regards!

Sumeluar

Mike Wilday

You're welcome!

Kiley Vander Wyst

Not sure if this thread will still get a response or not.. new to smartsheet and trying to figure out nested IF functions. I have tried several different solutions and the formula is still coming up "unparseable". The only other issues I can think of is that the cell that is being referenced is using a COUNTIFS formula and is cross-referencing another sheet. That formula works fine. Or that the cell does not have the right properties for RYG status indicator. This is for a calculations smartsheet that I am using for a dashboard, so the data is going across the rows instead of down the columns.

=IF([12/4/23-12/8/23]16 > 2), "Green", IF([12/4/23-12/8/23]16 = 1, "Yellow", "Red"))

Any help is greatly appreciated!

Genevieve P.

Hey @Kiley Vander Wyst

It looks like you're just closing off the first IF statement a bit too early!

Try this:

=IF([12/4/23-12/8/23]@row > 2, "Green", IF([12/4/23-12/8/23]@row = 1, "Yellow", "Red"))

If this hasn't helped, it would be useful to see a screen capture, but please block out sensitive data.

Cheers,

Genevieve

Kiley Vander Wyst

Hi @Genevieve P.

The formula works now but the result is only red despite the value in the reference cell being 2 some of the time. Any idea what could be causing that?

Best,

Kiley

Genevieve P.

Hi @Kiley Vander Wyst

The statements say:

If the value in this cell is greater than 2, "Green"
otherwise, if the value is exactly equal to 1, "Yellow"

Any other value will be Red, which includes anything less than 1 or from 1.1 - 1.9

Try:

=IF([12/4/23-12/8/23]@row >= 2, "Green", IF([12/4/23-12/8/23]@row >= 1, "Yellow", "Red"))

Cheers,

Genevieve

Kiley Vander Wyst

Hi @Genevieve P.!

Yes, that worked! I didn't know if I needed to add an AND function or not to the first IF function.

Thanks so much!

Kiley

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.