What am I missing in the formula?

Nana250

Here is the scenario:

Column 1 is the harvey ball symbol status - empty, quarter, three quarter, full

The goal is that each time one of the other columns is filled in with any date or a checkbox tick that the status changes.

Here is my current formula:

=IF(NOT(ISBLANK([Date Received]@row)), "Quarter", IF([CA Review Complete]@row = 1, "Half", IF([CA Approval Complete]@row = 1, "Three Quarter", IF(NOT(ISBLANK([Date Completed]@row)), "Full", IF(ISBLANK([Date Received]@row), "Empty")))))

The problem: it will only do one action at a time. It does not build. So if a date is filled in, the status changes to quarter, but if then the next column is ticked it does not change to half. If i delete the date then it will go to half.

Any help is greatly appreciated.

Hollie Green

Do it backwards start with the final "full" as your first if statement then back to Three Quarter then back to half then back to Quarter. The order of an If statement determines the next step and end with your Empty statement as it should only be empty if none of the other criteria are met.

Hollie Green

Do it backwards start with the final "full" as your first if statement then back to Three Quarter then back to half then back to Quarter. The order of an If statement determines the next step and end with your Empty statement as it should only be empty if none of the other criteria are met.

Nana250

Yes! this worked! I have a follow up question that maybe you can help me out with. I have this column linked to a metrics sheet to use in a dashboard.

Currently I have the top row that shows the total and the formula there is: =COUNTIF({Page Status}, "Quarter")

The next row is counting how many are in the next status. What I would like to do is now subtract from that first row, so by the time it goes to a row labeled "Full" or "Complete" the other rows are showing 0.

Genevieve P.

Hi @Nana250

Would you mind showing a screen capture of this metric sheet and clarify what exactly you want it to do?

Formulas are dynamic, which means that as your values change the number will change. This means that if you had a cell that was "Quarter" but then it changes, your COUNTIF statement will first say 1, then it will shift to 0 when the cell is changed to another value. Is this what you wanted?