Help Using Nested IF and COUNTIF formula that can be utilized for R. Y, and G Harvey Balls

Chad Dixon

I work with a team that serves our customers open enrollment needs. One of the key tasks is to track open enrollment.

When a case comes in from our customer, it provides us with three key pieces of information, in which our team should record in Smartsheet.

Part of the formula should look at these three fields and if one or more is blank, that read as though it is not complete.

The other part of the formula needs to take into account the OE begin date.

Both of these inputs (whether all 3 fields are entered AND reading from the OE begin date) should determine whether the status in R/Y/G. Here's the criteria:

• Green = The D365, PS Benefits Owner and PS Files Review columns are all populated in the tracking sheet OR at > 8 weeks from Anticipated OE Date.
• Yellow = The D365, PS Benefits Owner and PS Files Review columns are not all populated (e.g., none at all or at least one field missing), AND the project is between 6 – 8 weeks from the Anticipated OE Date. 
• Red = The D365, PS Benefits Owner and PS Files Review columns are not all populated (e.g., none at all or at least one field missing), AND the project is less than 6 weeks from the Anticipated OE Date.  

All of that needs to feed into a sheet summary because I want to report it in our operations dashboard. Something like this:

I would love your input!

Bassam Khalil

Hi @Chad Dixon

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

and it's better if you can share a sample of your data sheets on a sample workspace and share me as an admin on this workspace (after removing or replacing any sensitive information) then i will create the exact formula for you.

My Email: Bassam.k@mobilproject.it