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

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

=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 'contract update checkbox' is true or false is present?

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.

=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"), [Contract Update?]:[Contract Update?], <> 1)

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.