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

Nick Burrus
Nick Burrus ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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? 


Dr. St Nicholas Burrus DHA, PMP

I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!