Data derived from mulitple criteria
Hello Smartsheet Friends,
I have a single text box that I'd like to do some data analysis on, and wondering if there is a way. My text box is similar to the below
"Product, Engineering, IT, Supply Chain, etc......."
I want to pull all of the instances of "Supply Chain" and do a sumif. Aka total number of submissions for the Supply Chain department are X. Has anyone come across this, and if so what was the solution?
Thanks,
Arryn
Comments
-
Hi Arryn,
Do you want to count the number of times that "Supply Chain" is mentioned or do you want to sum a number that is connected with it?
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.
-
Hi Arryn,
If I understand what you are saying, the function you should be using if Countif. See more information on this https://help.smartsheet.com/function/countif -
Arryn
Can i suggest that a much better way to surface your required Satistics and KPi's would be in a dashboard with the ability to drill down by mouse click to the underlying data to interrogate those stats.
I do appreciate how rewarding it is to work out a really complex formula and it works! wow but why not just Keep it Simple so you can share the results easily with your team?
Hope that helps.
Richardr
SmarterBusinessProcesses.com
-
Yes! You all have it correct, I want to do a countif(s) BUT the caveat is the data is not consistent and located all in one cell. I've attached a sample. I would want something like Mobile IT - 3, Supply Chain/SC/Supply - 2 (based on the data set), etc. Parsing out a new line for each is not feasible (unless there is a formula), since this is a large data set.
Thanks,
Arryn
-
Happy to help!
Try something like this.
=COUNTIF([Teams Impacted]:[Teams Impacted]; FIND("Mobile IT"; @cell) > 0)
The same version but with the below changes for your and others convenience.
=COUNTIF([Teams Impacted]:[Teams Impacted], FIND("Mobile IT", @cell) > 0)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
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.
-
Yes, it sure did. Thank you!
-
Excellent!
Happy to help!
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
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!