Using IF function to return 3 different values to cell based on date

Hello!

I am currently using a Smartsheet for contract management. For the purposes of my question, it is three columns that are important. The Contract status, the contract start date, and contract cancel date, if and when applicable.

The three statuses I use are Active, Inactive, and Onboarding. Active needs to return "Active" when their start date is <= TODAY. Status needs to return "Onboarding" if start date >=TODAY. Both of these return values look at the Contract Start date column.

Then "Inactive" refers to the Cancel date column. If the Cancel date is blank, I would like it to return the current value it is in between the "Active" or 'Onboarding. Once an inactive date is present within the cell, I would like it to keep the current status in place until the cancel date is <=TODAY so it switches to say "Inactive".

I would think it would be odd to have a cancel date come before a contract is in 'Onboarding' because I would probably delete that row all together (that would be very rare anyhow).

I got this far : =IF([Cancellation Effective]@row <= TODAY(), "Inactive", IF([Start Date for Phase 1]@row <= TODAY(), "Active", IF([Start Date for Phase 1]@row >= TODAY(), "Onboarding")))

However when I clicked and began to drag it down, it would switch statuses that previously said "Active" (because I have been doing it manually from a drop down) it switched those to "Inactive" because I noticed the Cancel date cells for those was blank. I tried to nest an If BLANK function within it, but there is where I was stuck.

Thank you for any help!!!

Find more posts tagged with

Accepted answers

Paul Newcome

Blank date cells are considered less than TODAY(), so you would want to use an AND statement to include something about the [Cancellation Effective] column not being blank in the first IF.

=IF(AND([Cancellation Effective]@row <> "", [Cancellation Effective]@row <= TODAY()), "Inactive", IF([Start Date for Phase 1]@row <= TODAY(), "Active", IF([Start Date for Phase 1]@row >= TODAY(), "Onboarding")))

All comments

Paul Newcome

Let's give this one a whirl and see how it does...

=SUMIFS(Units:Units, [Property Status]:[Property Status], @cell <> "Inactive", [Status Change Reason]:[Status Change Reason], @cell <> "Contract Update")

Shelton Alves

I will give this a go- BTW - you are a rock star and a gentleman. Thank you for your help today. I will keep you posted!

Shelton Alves

Didn't do it-the number seemed very low. I assume the <> means everything except the word in quotes? I am trying to follow inside my sheet what its doing to come up with the number it did.

I think it may be easier to break this in to two formulas then add them together. Something like:

=SUMIFS(Units:Units, [Property Status]:[Property Status], OR(@cell = "Active", @cell = "Onboarding"), [Contract Update?]:[Contract Update?], what needs to go here to sum units if true or false is present?

The one above will ensure all active property units are summed whether it is a contract update or not and then sum all onboarding.

Next - Just count the Inactive where the status change reason column says everything but Contract Update (which is one of the three options in that drop down)

=SUMIFS(Units:Units, [Property Status]:[Property Status], "Inactive"), [Status Change Reason]:[Status Change Reason],"@cell<> "Contract Update")

The above is coming back as unparseable. How would I address summing check and unchecked contract update boxes in the first and then counting inactive that says everything except contract update?

Then those two formulas can be combined for the solution!

Paul Newcome

This last comment gave me a thought...

Will you ever have "Active" and "Contract Update" in the same row, or will all "Contract Updates" be "Inactive"?

If all "Contract Updates" will always be "Inactive", then couldn't we just SUM everything that isn't "Contract Update" and not worry about the [Property Status]?

=SUMIFS([Status Change Reason]:[Status Change Reason], @cell <> "Contract Update")

Shelton Alves

'Contract update' under the "Status Change Reason" column will always have the property's status be inactive. And the new parent row will be 'active' and the 'status change reason' blank with the 'contact update box checked. Property G would be an example of that in my screenshot above. I like where you are going with this!! I just tried and the unit count was very low,it came to a little over 2000 and the Active and Onboarding properties on their own have over 47,000. I like where this is going!!

Paul Newcome

It's low because I missed a part. Ugh. Let's try this one...

=SUMIFS(Units:Units, [Status Change Reason]:[Status Change Reason], @cell <> "Contract Update")

I use a lot of COUNTIFS functions and very little SUMIFS, so sometimes I get the syntax a little mixed up without even realizing it. Sorry about that.

Shelton Alves

Hey hey Paul!

So sorry for the delay-yesterday was a doozy. I did try this, but still got around 2000 units.

I think I will try this-if you are able to help me on the last piece-I will sum everything "Active" or Onboarding, and count them all regardless if 'contract update' checkbox is checked or not

Then:

=SUMIFS(Units:Units, [Property Status]:[Property Status], "Inactive"), [Status Change Reason]:[Status Change Reason],"@cell<> "Contract Update")

With the above, I will count all Inactives that have a reason other than 'Contract Update'. Then I think that should get me where I need to be to get the history, present, and future units correctly when I combine their sums together.

Random side bar too-and I do not know why I am having trouble here. I am trying to have a checkbox column be checked when a State column says "NC" (short for North Carolina) and that is the only state i want the check box to be checked for when it references the state column. All other abbreviations can be ignored. I keep getting unparsable with every iteration of an IF statement.

Paul Newcome

I just don't understand why this isn't working.

The Units column... How exactly is that data populated?

To completely disregard the checkbox, you can just remove it from the formula.

=SUMIFS(Units:Units, [Property Status]:[Property Status], OR(@cell = "Active", @cell = "Onboarding"))

Then the other has an extra quote and an extra closing parenthesis tucked in (both removed here):

=SUMIFS(Units:Units, [Property Status]:[Property Status], "Inactive", [Status Change Reason]:[Status Change Reason], @cell <> "Contract Update")

For the NC checkbox...

=IF([State Column]@row = "NC", 1)

Shelton Alves

Hello!

I was trying to get the following formula to work only when a box is unchecked in another column (column name is "Contract Update?". I don't want the calculation to sum the beds when a box is checked. What would be the best way to incorporate that here? Would it be an AND or IF function?

=SUMIFS(Beds:Beds, [Start Date for Phase 1]:[Start Date for Phase 1], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 12, 31)), [Property Status]:[Property Status], OR(@cell = "active", @cell = "onboarding"))

Paul Newcome

If you want to exclude those rows where the [Contract Update?] is unchecked, you would enter the range and criteria the same way you have the other range/criteria sets.

It looks like we can also simplify your date criteria to be a little more efficient by only looking at the year in the cell instead of having to compare to two dates.

=SUMIFS(Beds:Beds, [Start Date for Phase 1]:[Start Date for Phase 1], IFERROR(YEAR(@cell), 0) = 2020, [Property Status]:[Property Status], OR(@cell = "active", @cell = "onboarding"), [Contract Update?]:[Contract Update?], <> 1)

Shelton Alves

You are the best Paul!! Thank you so much. The first one is going to work better as it will help with the calculations once we get past 2020. But I appreciate you sending the formula for the second because it helps me learn more on what all can be done!

As always-thank you for your help!

Paul Newcome

Happy to help! 👍️

The second formula will actually work the same way as the first just slightly more efficiently on the back-end. It will only count where the dates are between Jan 1 and Dec 31 of 2020.

The difference is that it removes a function (and a set of parenthesis) and it cuts down on the chances of accidental typos since you only have to change the year number instead of two dates.

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.