Looking for a function that will count if multiple cells have 1 of 3 values
I have a Sheet that has a types: Next Release, Testing, In Development, Bug Fix, Backlog, and NEW. I am looking for a function to add to my summary that only counts In Progress types such as "Testing" and "In Development".
Answers
-
Hi @Troy G
I hope you're well and safe!
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
-
Here is a screen capture of my issues. Again I have a column titled "Type" that column is a drop down to show what type of enhancement it is for example Site Request, Not Prioritized (New), Developer idea, Ice Box. These types also control our card view lanes. When we assign these tasks and move from the backlog we change the type throughout its development stages examples In Development, Testing, Next Release, Completed, Bug Fix. I am needing a function for my summary panel that would count all the "Types" that we consider "In Progress" like Testing, Next Release, Bug Fix, and In Development. In addition, I would apply this same function to count all backlog items the have type Site request, Developer Idea, and Ice Box.
I do have In Progress and Backlog as a Parent and I found using the functions below gets me what I need but I can not always depend on the correct types being under the correct parent.
=COUNT(CHILDREN(Name1))
=COUNT(CHILDREN(Name21))
-
@Troy G One solution is you could add a "Status" column dropdown list that contains the verbiage you need (Not Started, In Progress, Backlog, etc.). Then you could create reports that categorize the rows similar to how you have them in your sheet.
Then you could create formulas that count the "Types" that have a certain "Status"
=Countifs(Type:Type, "Backlog", Status:Status, "In Progress"
This formula could be simplified in a metrics sheet, but these are some ideas to get the ball rolling
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!