Harvey Balls Using Formulas & Multiple Columns


Hi all!

I am trying to determine what formula to use when calculating Harvey Balls using two different column details. Harvey Balls are in the "Corrective Action" column.

What I want to do:

Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column

Harvey ball to be 3/4 full when text is in the "Response" column and "Done" is not checked

Harvey Ball to be 1/2 full when one "Done" is checked and "Response" has text

Harvey Ball to be empty when nothing is checked.

Thoughts if this is possible? I've taken some tries at this, but I keep getting error messages. Screenshot below!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Give something like this a try...

    =IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "") = COUNTIFS(CHILDREN(Done@row), OR(@cell = "", @cell <> "")), "Full", IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "")>= 1, "Half", "Empty"))

    If they are all checked and all have responses = "Full"

    If at least one is checked and has a response = "Half"

    All else = "Empty"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!