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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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. Well-done!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!