Data derived from mulitple criteria

APotter · 07/21/19

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å · 07/22/19

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 · 07/22/19

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 · 07/22/19

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 · 07/22/19

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å · 07/23/19

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 · 07/23/19

Yes, it sure did. Thank you!

Andrée Starå · 07/23/19

Excellent!

Happy to help!

Data derived from mulitple criteria

Comments

Help Article Resources

Categories