Sum range where relevant data may need to be first extracted from the cell

jessica.smith

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))

Genevieve P.

Hi @jessica.smith

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

Genevieve P.

Hi @jessica.smith

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

jessica.smith

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.

Genevieve P.

Hi @jessica.smith

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

jessica.smith

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