Count numbers separated by semi column/text in a sum formula

Nick Burrus

Hello in Column3 I have this as my first row:

 

="Total: " + SUM([Column3]2:[Column3]5) + " Ind & ↑: " + SUM([Column3]4:[Column3]5)

• [Column3]2: 3
• [Column3]3: 1; 1 IP
• [Column3]4: 4; 2 IP
• [Column3]5: 0; 1 IP

To keep aesthetics nice I don't want to break the cells up. Can someone help me get a formula that will add the two numbers together in the overall sum?

For exmaple "1; 1 IP" I want counted as "2". 

 

So Total: should read 12 and Ind & ↑: should read 7. Right now it skips over the field if it has a semi column or letters.

Anyone have any idea how to do this without adding any new cells or rows? 

 

Mike Wilday

Can you share a screenshot of your sheet or a link to a sample of it? 

Nick Burrus

https://app.smartsheet.com/b/publish?EQBCT=42ff492f8f4240b28027195cc38bf959

Paul Newcome

Did you find a solution to this?