Sum range where relevant data may need to be first extracted from the cell
I have a series of columns where users enter number of samples tested each month. They can indicate the number of batches in the same column using a delimeter. E.g., 4(2) means 4 samples were tested in 2 batches. We cannot add a separate column to indicate batches.
I want to sum the total number of samples across the range of columns.
This formula works but I want shorten it up. Any ideas?
Conceptually:
Formula:
=SUM(IF(CONTAINS("(", A@row), VALUE(SUBSTITUTE(LEFT(A@row, FIND("(", A@row)  1), " ", "")), A@row), IF(CONTAINS("(", B@row), VALUE(SUBSTITUTE(LEFT(B@row, FIND("(", B@row)  1), " ", "")), B@row), IF(CONTAINS("(", C@row), VALUE(SUBSTITUTE(LEFT(C@row, FIND("(", C@row)  1), " ", "")), C@row), IF(CONTAINS("(", D@row), VALUE(SUBSTITUTE(LEFT(D@row, FIND("(", D@row)  1), " ", "")), D@row), IF(CONTAINS("(", E@row), VALUE(SUBSTITUTE(LEFT(E@row, FIND("(", E@row)  1), " ", "")), E@row))
Best Answer

Since there's possibly a combination of text  the "("  and numbers in each cell, this means each cell needs to be evaluated individually.
As an alternative to doing this in one long formula, you could have a helper column each year that looks at 12 columns in a row, then at the end of the sheet simply SUM the 5 helper Year columns together. You can hide the helper columns in the sheet or keep them as yearly summaries.
The alternative would be to add a new row for each batch so you only ever have numbers entered. This means row 1 in your example would turn into two rows:
Row 1: Batch 1  2 / 4 / 3 / 1 / 5
Row 2: Batch 2  0 / 0 / 0 / 1 / 0
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

If this is the only way that the data can be entered, then that formula is exactly what I would have suggested.
You'll need to check each individual cell for the ( symbol since it may or may not appear in each cell, and in each instance only take what's to the left of that symbol, using the VALUE function to ensure it's a value. Welldone!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks @Genevieve P.! The issue is that this formula isnt super scalable. I'll have one column for every month for 5 years at a time in my spreadsheet once I scale my solution, so I'm going to hit cell character limits. It would be great if I could incorporate the same "extraction" of the sample number for all cells within a range rathar than having to repeat the syntax for each specific column.

Since there's possibly a combination of text  the "("  and numbers in each cell, this means each cell needs to be evaluated individually.
As an alternative to doing this in one long formula, you could have a helper column each year that looks at 12 columns in a row, then at the end of the sheet simply SUM the 5 helper Year columns together. You can hide the helper columns in the sheet or keep them as yearly summaries.
The alternative would be to add a new row for each batch so you only ever have numbers entered. This means row 1 in your example would turn into two rows:
Row 1: Batch 1  2 / 4 / 3 / 1 / 5
Row 2: Batch 2  0 / 0 / 0 / 1 / 0
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hey @Genevieve P. ,
Separate helper columns for each year is a good idea. I think that will work for our needs. Thanks so much for the assistance!
Jess
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!