Formula Improvement

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.
Comments
-
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
-
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, andree@getdone.se)
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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.
-
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.
-
Thanks!
I saw that you already got an excellent answer.
Let me know if I can help in any way!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!