Sum range of numbers within a range of cells separated by multiple /'s

[Squidlo]

Hello, A few months ago I was assisted with this issue "Sum range of numbers within a range of cells separated by /". I was wondering if this can be expanded to add another set of numbers separated by another /? I know adding a column would be a great solution, but this data is part of a larger table and I need to maintain the structure if at all possible.
Basically, sum both column and row with data formatted like this 2/2/1
Here is the current formula that hemesh previously provided and works great for data with only one /. i.e. (2/1) (Thanks again hemesh! ):

Please Login or Register  to view this content.

I have attached the file Helpbook3.xlsx and highlighted the cells I would like to revise the formulas in.

Cell B49's formula currently results in 7/5. I would like it to result in 7/7/5. And I44 would = 33/32/24

Thank you in advance, any assistance is greatly appreciated.

Regards,
Squidlo

[ben_hensel]

Where are you getting these numbers from?

I encourage you to store numbers as numbers instead of as bits inside text strings, especially when "12/12/8" is going to implicitly be a date instead of... whatever you are doing.

"One-and-only-one value per cell" is a good rule.

I think you'll be better off fixing your data model (and fixing what you have with text to columns) instead of trying to work around the existing problems.

[Squidlo]

Hi Ben, Thank you for your response. I hear what you are saying, and I have tried expressing the same the higher ups at my company. Unfortunately, they want the all the different data types to fit nice and pretty in each section.

Currently the data is collected for each division by month. This section deals with CQFI's Received (1st number)/ Accepted (2nd number)/ and Closed (3rd number). All the other sections not shown in the helpbook have only one value per cell. They want to be able to view the data as it is listed. We could simply update the text manually, but some people can't add very well as that is how I came to searching for a formula in the first place. It was all working great until someone had the idea of adding Closed to the monthly list.

[ben_hensel]

Guh, okay, we'll use SEARCH like fifty times, then.

In cell I37, insert this array formula:

Please Login or Register  to view this content.

EDIT:
Wait, I messed up the error handling on the third term, it should be good now.

[Squidlo]

Thanks Ben, I really appreciate your efforts.

I inserted the array formula into cell I38 and it started out great with the exception of the rows with incomplete data (only the last data set is displayed "/12"). But then I populated row I45 and it omits the middle number. Not sure what is different. Helpbook4.xlsx

Is it possible to have the running total when some of the cells are blank? Not all facilities promptly update their data and I would also like to apply the formula to the column totals which are monthly.

Thanks again Ben,

[ben_hensel]

Okay, here's another comically long array formula to go in I37:

Please Login or Register  to view this content.

This will default to displaying "0/0/0" as the output.

[Squidlo]

That works great! Thanks for humoring myself and my coworkers.