Formula Improvement

Ann S

Hello!

I am trying to write a formula to count whether a group is active or inactive and how many groups are active. There are a couple conditions though, column1: the cell must not be blank (if it blank, it should not count), column2 **is a dropdown menu with three choices** : the cell must either be blank or contain "training" to be counted. I want to count the group of 5 rows. If ALL FIVE ROWS are inactive it will need to count as inactive *it will also need to be aware if not all 5 rows are filled out but the ones that are filled out are inactive*. This will then need to be repeated for seven more groups. 

 

I attached a screenshot of the idea I'm thinking of. So crew 1 would still need to be counted as active while crew 2 is inactive. The name and status is submitted via a report and the level is found with a VLOOKUP.

L_123

need more info. do you have dropdowns? What are your column names? how is your sheet sorted? How do you add information to the sheet?

 

Some screenshots would help. What you are describing is very much possible in smartsheet, but the solution depends on how you have the sheet organized

Andrée Starå

Hi Ann,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Ann S

This is how it is currently laid out. I will add colors and that kind of formatting afterwards. The name and status is submitted via a report and the level is found with a VLOOKUP.

Ann S

This is how it is currently laid out. I will add colors and that kind of formatting afterwards. The name and status is submitted via a report and the level is found with a VLOOKUP.

L_123

The easiest way is to create dropdowns and reference them as parents and children. If you do that you can collapse them and drag down the formula, and they will always reference the correct ranges.

The formula would look something like

=countifs(children([Yoga Crew Status]@row),or(@cell = "", @cell = "Off"))

 

You could throw in an if statement in there to return different remarks based on the number of people in the group that are out, or use conditional formatting to highlight the return based on the different values.

Andrée Starå

Thanks!

I saw that you already got an excellent answer.

Let me know if I can help in any way!

Best,

Andrée