Need help with combining a formula that has less than, greater than date

Terri

I have a formula on row 8 that works with a date that is greater than 3/6/23 (Visit Date) based on criteria from 3 columns and outputs the response in the Overall Status column. This formula uses data from the 2nd, 3rd and 4th columns:

• 2nd column = Prob List & Notes Status - (has 2 drop down selections)
• 3rd column = Med/Hx/Sticky Note Status - (has 2 drop down selections)
• 4th column = Allergy Status - (has 2 drop down selections)

I also have another formula on row 9 that works with a date that is less than 3/6/23 (Visit Date) based on criteria from 3 columns and outputs the response in the Overall Status column. This formula uses data from the 2nd, 3rd and 5th columns:

• 2nd column = Prob List & Notes Status - (has 2 drop down selections)
• 3rd column = Med/Hx/Sticky Note Status - (has 2 drop down selections)
• 5th column = Notes Status (if not done w/ Prop List) - (has 2 drop down selections)

What I would like to do is to combine both formulas into one, so that the Overall Status displays the response based on if the Visit Date is greater than or equal to 3/6/23 AND the 2nd, 3rd & 4th columns or less than 3/6/23 AND the 2nd, 3rd & 5th columns.

I don't know if this is possible or not and I have spent a lot of time trying to get this to work with no luck. If anyone can help me, I'd really appreciate it. I've attached screenshots that may help.

Thanks,

Terri

Lucas Rayala

Hi @Terri, the TLDR is, I think you can use this formula, which you should set as a column formula (read below for details):

=IF([Visit Date]@row<=DATE(2023,03,06), IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress")))

Here's the details. To start, I commend you for tackling all the variable with these intricate nested IF statements, but let's do a couple of things to simplify things. For starters, what does the date 3/6/2023 mean to you? There is a function called "TODAY" that allows you to generate a date based off today's date -- is this a case when you are looking five days in the future? If so, you can replace your static date with TODAY(5) so you don't have to change it all the time. If it's another criteria (i.e. always "Monday), let me know and we can make that happen.

Regarding the formula -- can I assume that the criteria for your "Overall Status" is the same for every row? If so, we can use a column formula, which applies the same formula to every row. You do that by right clicking the cell with your formula, then selecting "Make Column Formula". Don't do it yet! Let's fix your formula first.

I think this is your logic--can you confirm?:

• If all statuses are blank, then leave the status blank.
• If all the statuses are "Complete", then the status is complete
• Otherwise, in all other cases, the status is "In Progress".

If so, let's set the status formula up a little differently (I'm going to ignore the date for a moment and we'll add that in at the end). I'm using "COUNTIFS", which counts the number of times a certain event occurs in a range. For the range, I'm using the first and last column name with an @row to signify that I'm looking only at this row -- I put a colon between them to indicate I want the formula to look at every column between them on the same row (in this case it's just the additional middle column, but the range is still easier than three different statements).

=IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress"))

First, I'm counting how many of the three status cells are blank (double quotes around nothing means "blank"). If they're all blank (i.e. we count "3" instances of "blank cells" in that range), then we add "".

Then we do the same thing, except we count how many times we see the status "Complete" -- if it's 3, then we know it's "Complete".

All other statuses are "In Progress".

Now let's add your date back in (I've used "TODAY(5)" to show you how that looks, but you can change that back to DATE(2023, 03, 06) if you like):

=IF([Visit Date]@row < TODAY(5), IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress")))

Now make that formula a column formula.

Let me know if this works, and mark this post as the answer if we got it right!

Lucas Rayala

@Terri, perfect! I'm glad you got that worked out. And that was a clever way to ensure one simple formula covered both your issues. If you run into a problem with slightly varied language again ("Complete" vs "Completed"), instead of asking your formula to look for an exact match, which is what you are doing with "Complete", you can replace that with a CONTAINS function to say, "I want to get cells that contain this word". I use this all the time when I don't trust how data may be inputted. That would look like this (the relevant portion is bolded):

=IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, "") = 4, "", IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, CONTAINS("Complet", @cell)) = 3, "Complete", "In Process"))

The @cell reference is used when you use CONTAINS as a criteria within another formula. Notice I only used part of the word, so that I would find both "Completed" and "Complete".

Lucas Rayala

Hi @Terri, the TLDR is, I think you can use this formula, which you should set as a column formula (read below for details):

=IF([Visit Date]@row<=DATE(2023,03,06), IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress")))

Here's the details. To start, I commend you for tackling all the variable with these intricate nested IF statements, but let's do a couple of things to simplify things. For starters, what does the date 3/6/2023 mean to you? There is a function called "TODAY" that allows you to generate a date based off today's date -- is this a case when you are looking five days in the future? If so, you can replace your static date with TODAY(5) so you don't have to change it all the time. If it's another criteria (i.e. always "Monday), let me know and we can make that happen.

Regarding the formula -- can I assume that the criteria for your "Overall Status" is the same for every row? If so, we can use a column formula, which applies the same formula to every row. You do that by right clicking the cell with your formula, then selecting "Make Column Formula". Don't do it yet! Let's fix your formula first.

I think this is your logic--can you confirm?:

• If all statuses are blank, then leave the status blank.
• If all the statuses are "Complete", then the status is complete
• Otherwise, in all other cases, the status is "In Progress".

If so, let's set the status formula up a little differently (I'm going to ignore the date for a moment and we'll add that in at the end). I'm using "COUNTIFS", which counts the number of times a certain event occurs in a range. For the range, I'm using the first and last column name with an @row to signify that I'm looking only at this row -- I put a colon between them to indicate I want the formula to look at every column between them on the same row (in this case it's just the additional middle column, but the range is still easier than three different statements).

=IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress"))

First, I'm counting how many of the three status cells are blank (double quotes around nothing means "blank"). If they're all blank (i.e. we count "3" instances of "blank cells" in that range), then we add "".

Then we do the same thing, except we count how many times we see the status "Complete" -- if it's 3, then we know it's "Complete".

All other statuses are "In Progress".

Now let's add your date back in (I've used "TODAY(5)" to show you how that looks, but you can change that back to DATE(2023, 03, 06) if you like):

=IF([Visit Date]@row < TODAY(5), IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "")=3,"" ,
IF(COUNTIFS([Prob List & Notes Status]@row:[Allergy Status]@row, "Complete")=3,"Complete", "In Progress")))

Now make that formula a column formula.

Let me know if this works, and mark this post as the answer if we got it right!

Terri

Hi Lucas,

Thank you so much for tackling this for me and for the compliment. The formula is definitely a beast in which I needed to capture all of the different scenarios and the shortened version you came up with looks a lot easier. Your explanation makes sense and is easy to follow. I really appreciate you taking the time to help me with this. It is so close to what I'm trying to achieve.

To answer your question about the date ... we want to begin using the Allergy Status column effective on 3/6/23, but we also need to preserve the calculations we made prior to 3/6/23 based on the Notes Status (if not done w/ Prop List) column. If there's a way to incorporate the greater than 3/5/23 date, but less than the 3/6/23 date, that would help a lot.

I extended your formula to include the Notes Status column and changed the number to 4. By doing this, I was thinking of hiding the Notes Status column starting on 3/6/23, so that we don't use it, but the problem with that is that we still need to see it. Then I thought I could move the column over to the far right, but then I got a circular reference error.

You are correct that:

• If all statuses are blank, then leave the status blank.
• If all the statuses are "Complete", then the status is complete
• Otherwise, in all other cases, the status is "In Progress" - this should actually read as "In Process"

Another thing I noticed on my end is that the Prob List & Notes Status column is the only column that contains the word "Complete", whereas the other 3 columns contain the word, "Completed". It's ok that the Overall Status outputs "Complete" as the result, but is there a way to add in the last IF(COUNTIFS argument that if the range of columns contains any of the word Complete, so that it's picking up the word Complete and Completed? If not, I can ask my colleague to have that one column changed to the word Completed and then modify your formula.

Thanks for your help on this!

Terri

Terri

@Lucas Rayala - after using your formula and trying to tweak it to include the less than or greater than Visit Date, the light bulb in my head finally clicked on! Your formula was what I needed, but without the date piece. I don't know why I didn't realize this sooner.

I used your COUNTIFS statement, but extended it to include all 4 columns. For the blank cells, instead of using =3, I used =4 because all 4 cells would have to be blank to return a blank Overall Status. When I tried using =3, it was returning "In Process" even though all 4 cells were blank.

Expanding that thought process, it dawned on me that the greater than, less than Visit Date doesn't matter now because if the Allergy Status column will be effective on 3/6, the COUNTIFS statement you explained will be searching for 3 instances within the 4 columns on that particular row. So, if there are selections made effective 3/6 and after in the first 3 columns (after the Visit Date column), then I still receive the correct Overall Status. The same is true for anything prior to 3/6 in which selections were made in the first 2 columns and then the 4th column. Because it's still searching for 3 instances within the 4 columns, it also returns the correct Overall Status.

This is your formula that I ended up using with some modifications and below that are screenshots for you to see what it looks like:

=IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, "") = 4, "", IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, "Completed") = 3, "Complete", "In Process"))

Thank you again for your help with this!! I really appreciate it. 😊

Terri

Lucas Rayala

@Terri, perfect! I'm glad you got that worked out. And that was a clever way to ensure one simple formula covered both your issues. If you run into a problem with slightly varied language again ("Complete" vs "Completed"), instead of asking your formula to look for an exact match, which is what you are doing with "Complete", you can replace that with a CONTAINS function to say, "I want to get cells that contain this word". I use this all the time when I don't trust how data may be inputted. That would look like this (the relevant portion is bolded):

=IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, "") = 4, "", IF(COUNTIFS([Prob List & Notes Status]@row:[Notes Status (If not done w/ Prop List)]@row, CONTAINS("Complet", @cell)) = 3, "Complete", "In Process"))

The @cell reference is used when you use CONTAINS as a criteria within another formula. Notice I only used part of the word, so that I would find both "Completed" and "Complete".

Terri

@Lucas Rayala, this is awesome!! I will try out your CONTAINS formula and thanks for explaining the @cell reference ... I didn't know that before. I cannot thank you enough for all your help with this. 🤗

Terri

Lucas Rayala

@Terri, glad everything worked out 🙂