Sum of values across Arrays/Lists

Hi there,
I have a column that shows a set of values like this:
COLUMN A
Row 1 20:40:0:0:20:0
Row 2 2:4:0:0:0:4
Row 3 . 0:0:0:0:0:0
etc.
Each row represents a project task.
The value represents the number of employees needed for the task across 6 different employee types/categories (categories separated by ":").
I need a formula the generates a SUM of the respective categories across all rows.
In this example the result would be:
Result 22:44:0:0:20:4
Any advice is greatly appreciated
Comments
-
A less than ideal work around would be to parse out the values into different columns, then add them up and then re-join/concatenate back to the original format.
-
Parsing, summing, then concatenating would actually be the best way to go as it would provide the most accuracy.
Help Article Resources
Categories
Check out the Formula Handbook template!