Sum Formula with other data is cells
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
Best Answer

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 needSUM(), 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
Answers

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 needSUM(), 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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!