I am trying to count how many milestones are Green, Red, and Yellow

Mark Sleighter

Hopefully, someone can help me out. I and trying to create a summary of how many milestones are yellow, red, and green

Milestones Yellow -?

Milestones Green -?

Milestones Red -?

I have the formula to count milestones - =COUNTIF({Sheet Name Range 3}, "1"), and I get an accurate count of all milestones.

I also have the formula to count the text of the color =COUNTIF({Sheet Name Range 1}, "Red"), and I get an accurate count. However, I am unable to put the two together so I can count how many milestones are red, green, and yellow.

Your help is greatly appreciated.

Summer

Hi @Mark Sleighter,

May not be the cleanest approach but you can just add all your COUNTIF formulas together.

=COUNTIF({Sheet Name Range 3}, "Red") + (COUNTIF({Sheet Name Range 3}, "Green") + (COUNTIF({Sheet Name Range 3}, "Yellow")))

Mark Sleighter

Hi @Summer,

Thank you for taking the time to try and help, but that is not exactly what I am looking for, as I may not have explained the use case properly.

In the screenshot below, I have the milestone column and the health column. I want to count how many milestones (stars) are green, how many milestones are yellow, and how many milestones are red. Then once I have this on a summary sheet, I can add a graph to my dashboard.

Hopefully, this will provide a better understanding of my question and the solution I am looking for.

Thank you for getting back to me so quickly. I have the counts

Sing C

Hi @Mark Sleighter,

I assume you have 3 sheet summary fields to capture the numbers, one for each of Red, Yellow, and Green milestones.

You can use the COUNTIFS formula to count the number of rows that meet multiple criteria - in this case, is a milestone and is red; is a milestone and is yellow; is a milestone and is green.

Here is the formula for red:

=COUNTIFS(milestone:milestone, 1, Health:Health, "Red")

You can get the official syntax and usage notes here: COUNTIFS Function | Smartsheet Learning Center

I hope this helps!

Regards,

@Sing C

Mark Sleighter

@Sing C , Thank you for responding. However, that did not work either.

Sing C

@Mark Sleighter Do you get an error message in the formula? Here is a screenshot of the test sheet I created, with the formula in the sheet summary field for Red milestones, and the formula correctly evaluates to 2.