Roll Up Harvey Balls

cmcneillyjci

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")

L_123

Missing parenthesis in last countif (also quote on number)

Current:

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

Should be

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

Paul Newcome

=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.

cmcneillyjci

Thanks so much!  

Paul Newcome

Happy to help.