SUM a value found in a string within each cell across a range?
Due to memory limitations i have all projects rolling up some user metrics data into a single cell per project. This cell has a text string with a value, followed by another text string and value, over and over for all my variables, for example, each project will have this:
Person A status /0 Person A task risk /5 Person A task open 8 Person B status /3 Person B task risk /2 Person B open 4
I now have another sheet that is setup per person, eg Person A and Person B are 2 rows, with the columns being Status, Task Risk, Open.
I am trying to figure out a way to use the Person sheet to do a SUM or SUM/COLLECT or something to sum the values of all of Person A's Status across all projects. And then the same for Task Risk and Open, for each resource.
I have a MID formula that can easily extract the values if i created a helper column for each person+variable, but then if i have 50+ people its too many columns to manage. So im trying to do this by essentially SUM a variable found within a string inside a cell for each cell in the range
is this possible?
Best Answer
-
You are going to need to parse those numbers out to be able to SUM them.
Answers
-
You are going to need to parse those numbers out to be able to SUM them.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!