SUM a value found in a string within each cell across a range?

01/15/22
Accepted

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?

Tags:

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

    You are going to need to parse those numbers out to be able to SUM them.

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

    You are going to need to parse those numbers out to be able to SUM them.

    thinkspi.com

Sign In or Register to comment.