# Roll Up Harvey Balls

Options
✭✭✭
edited 12/09/19

I am currently working on a tracking page where I've been asked to create a "Progress" column using Harvey Balls.  The goal is to use empty, half and full to represent not started, in progress and complete by training module (children) and then overall.  Below is one of the many formulas I've been working on.

=IF(AND(COUNTIF(CHILDREN(), "Half") = 0, COUNTIF(CHILDREN(), "Empty") = 0), "Full", "Empty")

(AND(COUNTIF(CHILDREN(), "Half") = 1, COUNTIF(CHILDREN(), "Full"= 0", "Half", "Empty")

Tags:

• ✭✭✭✭✭✭
edited 02/25/19
Options

Missing parenthesis in last countif (also quote on number)

Current:

COUNTIF(CHILDREN(), "Full"= 0"

Should be

COUNTIF(CHILDREN(), "Full")= 0

• ✭✭✭✭✭✭
Options

=IF(OR(COUNTIFS(CHILDREN(), "Half") > 0, AND(COUNTIFS(CHILDREN(), "Empty") > 0, COUNTIFS(CHILDREN(), "Full") > 0)), "Half", IF(COUNTIFS(CHILDREN(), "Full") = COUNT(CHILDREN()), "Full", "Empty"))

First it will look for any Half's or a combination of Full's and Empty's. If it finds either of those two things, it will show as Half/In Progress. If it does not find either of those, it will count how many are Full. If the count of full is the same as the count of the children (meaning all Full), then it will show as Full/Complete. If it isn't either of those two then it will show Empty/Not Started.

• ✭✭✭
Options

Thanks so much!

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!