Sum Formula with other data is cells

Michael Halvey

Hello - Hopefully this is a simple question. I have a column that I am trying to sum the data in but the cells quantities and data inside each cell. Is it possible to sum the total quantity for the column?

I am trying to avoid using a helper column to move the number out and sum off that if possible. The quantity's can also be anywhere between single to triple digit numbers.

Example: How do I show on a sheet summary cell that there are 66 items in total or more if there are more rows.

Any help on this would be great, I have a feeling I'll have to do a helper column but I'm trying to avoid it if a formula can handle it all for me.

-Michael

Toufong Vang

Hi, Michael.

For what you're trying to do, it is impractical to not use a helper column. The range you're interested in contains strings. The Smartsheet functions you need--SUM(), SUMIF(), SUMIFS()--are performed on numbers, whether individually or in ranges.

It's possible to avoid a helper column. However, before you can do math with data from that column, you'll need to parse the numeric characters from the string AND convert those characters into numeric values. Without a helper column, your SUM() formula will need to include instructions for doing BOTH those things on EACH CELL before it adds up the values. Even for 6 rows, that would be a lot of typing!

As you noted, a column formula can be used to extract the numeric value you need from the string. Once that is done, a simple SUM() formula can be performed on the entire column (range). If the numerals you're interested in precede " -", then your column formula might be:

= VALUE( LEFT([Column_XX]@row, FIND(" -",[Column_XX]@row)-1))

This will retrieve the digits that appear before " -" regardless of how many there are.

Hope this helps!

-TV

Toufong Vang

Hi, Michael.

For what you're trying to do, it is impractical to not use a helper column. The range you're interested in contains strings. The Smartsheet functions you need--SUM(), SUMIF(), SUMIFS()--are performed on numbers, whether individually or in ranges.

It's possible to avoid a helper column. However, before you can do math with data from that column, you'll need to parse the numeric characters from the string AND convert those characters into numeric values. Without a helper column, your SUM() formula will need to include instructions for doing BOTH those things on EACH CELL before it adds up the values. Even for 6 rows, that would be a lot of typing!

As you noted, a column formula can be used to extract the numeric value you need from the string. Once that is done, a simple SUM() formula can be performed on the entire column (range). If the numerals you're interested in precede " -", then your column formula might be:

= VALUE( LEFT([Column_XX]@row, FIND(" -",[Column_XX]@row)-1))

This will retrieve the digits that appear before " -" regardless of how many there are.

Hope this helps!

-TV