Automating Status Balls to Showcase Overall Health

Hi Shay,

You can do this by using a COUNTIF formula to count each colour, then create rules based on which is larger. Try something like this:

=IF(OR(COUNTIF(Status:Status, "Red") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Yellow")), (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")) >= COUNTIF(Status:Status, "Green")), "Red", IF(OR(COUNTIF(Status:Status, "Yellow") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Red")), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Green"), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Yellow"), COUNTIF(Status:Status, "Green") = COUNTIF(Status:Status, "Yellow")), "Yellow", IF(COUNTIF(Status:Status, "Green") >= (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")), "Green", "Green")))

Here's what it says broken down:

1 . Red Rules:

=IF(OR(COUNTIF(Status:Status, "Red") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Yellow")), (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")) >= COUNTIF(Status:Status, "Green")), "Red",

If there are more Red status balls (or equal to) than the number of Green and Yellow balls put together, return Red.

or

If the total of both Red and Yellow balls is greater than or equal to the number of Green balls, return Red.

(Keep in mind that this will return "Red" even if there are only Yellow and Green balls, but more Yellow than Green)

2 . Yellow Rules:

IF(OR(COUNTIF(Status:Status, "Yellow") >= (COUNTIF(Status:Status, "Green") + COUNTIF(Status:Status, "Red")), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Green"), COUNTIF(Status:Status, "Red") = COUNTIF(Status:Status, "Yellow"), COUNTIF(Status:Status, "Green") = COUNTIF(Status:Status, "Yellow")), "Yellow",

There are four rules here that will return a Yellow Ball:

If there are more (or equal to) Yellow balls than Red or Green put together.

OR If there are the same number of Red and Green balls.

OR If there are the same number of Red and Yellow balls.

OR If there are the same number of Green and Yellow balls.

3 . Green Rules:

IF(COUNTIF(Status:Status, "Green") >= (COUNTIF(Status:Status, "Red") + COUNTIF(Status:Status, "Yellow")), "Green", "Green")))

If there are more Green balls than Red and Yellow put together, return Green. If none of the rules above apply, return Green.

Alternate Option:

Instead of building out this massive formula, you might want to consider creating 4 smaller formulas. One to count Red, one to count Yellow, one to count Green, and then one to compare the totals and return a specific colour based on that. The fourth formula would be similar to the one above (a Nested IF statement), but it since you're removing the embedded "COUNTIF" portion of it, should you need to change anything in the future it might be easier to read. Let me know if you would like to see an example of this and I'm happy to help explain further.

Help Center Articles

Here are some articles I used to help build this: IF Function / OR Function / COUNTIF Function/ Formula Operators

Hi @Susie Wachowski

I would suggest going with my Alternate Option at the end of the post, if you're looking to use the gray ball as well.

You can just build one formula per ball that's like this:

=COUNTIF(Status:Status, "Green")

then in another cell:

=COUNTIF(Status:Status, "Gray")

Etc... make sure you spell Gray with an A or it won't recognize it and count 0.

Then in your final 5th cell you can set up your rules for what to do, depending on what's showing. Can you go into a bit more detail of what you're looking to do? Do you just want to display what status has the most total numbers?

Hi @Lori Drum

In this instance you would use COUNTIFS (with an S) and add in that criteria and column into each of the statements.

For example:

=IF(OR(COUNTIFS(Status:Status, "Red", Type:Type, "Phase") >= (COUNTIFS(Status:Status, "Green", Type:Type, "Phase")...etc

Let me know if this works for you. 🙂

Cheers,

Genevieve

Hi @Kate Kelley

What helps me with formulas is breaking down each individual IF statement to read what the logic is. Any time there's an IF, separate that out. Then we can look at the order of the statements and make sure none of them are blocking each other.

Your Red Statement:

=IF(OR(COUNTIFS(Children(), "Red") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Yellow")), (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")) >= COUNTIFS(Children(), "Green")), "Red",

In words:

• If there are More Red Children than there are Green and Yellow combined
• OR if there are more Red and Yellow Children than there are Green in total, return Red.

So far so good!

Your Yellow Statement:

IF(OR(COUNTIFS(Children(), "Yellow") >= (COUNTIFS(Children(), "Green") + COUNTIFS(Children(), "Red")), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Green"), COUNTIFS(Children(), "Red") = COUNTIFS(Children(), "Yellow"), COUNTIFS(Children(), "Green") = COUNTIFS(Children(), "Yellow")), "Yellow",

• If there are more Yellow Children than Green + Red,
• OR if the number of Red and Green Children are Equal,
• OR if the number of Red and Yellow Children are Equal (note, this is why you're getting Yellow for all Green rows. Red and Yellow both = 0 in this case)
• OR if the number of Yellow and Green Children are Equal, return Yellow.

Your Green Statement:

IF(COUNTIFS(Children(), "Green") >= (COUNTIFS(Children(), "Red") + COUNTIFS(Children(), "Yellow")), "Green", "Green")))

• If there are more Green children than Red + Yellow, return Green

It looks like what's getting in your way is the order of the statements. We'll want to put Green first, so that it first looks for Green Children. This will overwrite the OR statement in your Yellow one that's giving you Yellow when the whole thing is Green.

Next, let's do the Yellow Statement. This is because the RED statement says "if there are more Red and Yellow Children than there are Green in total, return Red." This means that if you have ONLY Yellow Children, the top will turn Red, not Yellow, so we want this to be the last thing to check after the Yellow criteria.

Here's the exact same formula and statements, just rearranged!

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green")))

The Gray status is a bit trickier; do you want these rows to be skipped completely? I would maybe suggest adding just one statement that says if all the Children are Gray, return Gray, otherwise ignore these cells in the rollup?

=IF(COUNTIFS(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green"))))

Cheers,

Genevieve