Data derived from mulitple criteria

APotter

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 

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!