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
 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
 123 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!