Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Countifs formula using or

Options
✭✭✭✭
edited 12/09/19

Hello.  I am trying to count the RYG ball (in this case red) for each team.  One of our teams is comprised of three sub-teams, so I need for it to be counted if any of those show a red ball.  How do I create or for the Team section of this formula?

=COUNTIFS([Overall KPO Status]:[Overall KPO Status], "Red", Team:Team, {"Loyalty", "Partnership", "Portfolio Marketings"})

I searched the community for similar questions and checked the Smarsheet Formula Examples sheet, but I still can figure it out.

Any assistance would be greatly appreciated.

Tags:

Comments

• Options

Try:

=COUNTIFS(CHILDREN(), "Red", CHILDREN(Team:Team), "Loyalty")

• ✭✭✭✭
Options

Thank you!

• ✭✭✭✭
Options

My solution in April was to break up the formulas for red, yellow, and green for each sub-team.

=COUNTIFS([Overall KPO Status]:[Overall KPO Status], "Red", Team:Team, "Sub-Team1")

=COUNTIFS([Overall KPO Status]:[Overall KPO Status], "Red", Team:Team, "Sub-Team2")

=COUNTIFS([Overall KPO Status]:[Overall KPO Status], "Red", Team:Team, "Sub-Team3")

And then, I summed the children rows for each color individually for the overall team.

=SUM(CHILDREN())

That ended up being 16 rows worth of formulas when I was hoping to use 3 rows by creating one formula for each color to include all the sub-teams.

Does that make sense?

This discussion has been closed.