Roll Up Harvey Balls

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…