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

Find more posts tagged with

Finance

Formulas

Project Management

Comments

Andrée Starå

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

bukolatadio

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

Richard Rymill SBP

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

APotter

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

Andrée Starå

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?

APotter

Yes, it sure did. Thank you!

Andrée Starå

Excellent!

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…