Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
How to Sum Cells with Numbers & Text
Have a super basic question I just can't seem to find a quick answer for.
Want to sum cells with both numbers and text in them like "15 min" + "15 min" and get the result of "30 min". I formatted the cell with the following formula:
=15 + " min"
Result: 15 min
When I add 2 cells with that formula [=15 + " min"] + [=15 + " min"], the result is 0. How do I tell smartsheet to ignore the text string and focus on summing the numeric value only?
Comments
-
SmSulli ✭
I would suggest you separate the numbers and the units. This will make your calculations and data entry much easier. See screen shot included. I've used =sum(children()) in the parent row to total the time.
Shawn
-
Mike Wilday ✭✭✭✭✭✭I second Shawn's solution. I don't believe that summing text columns is possible. At least, not without some kind of fancy dissecting of the cells.
-
Dereck H ✭
Thank you for the feedback guys, just wanted to confirm and see if there was a way to get the formatting I was looking for without haveing to add a separate column. If anyone is aware of a "fancy" formula workout, I'm all eyes & ears.
-
If there is always only 1 space in the text, and the duration is always in minutes, and the answer is in minutes, you can find the cell number value like this:
=VALUE(LEFT(Duration1,FIND(Duration1," ")-1))
If you have varying hours, minutes, second, hours entries, you can pull out the units with this:
=RIGHT(Duration1,LEN(Duration1)-FIND(Duration1," "))
Does that set you in the right direction?
Jim
-
Jim,
We're looking for a formula in a cell to sum the number in 2 parent cells where we have text and numbers in the same cells as a result of a formula. Currently, we're trying to a workaround of SUMIF & IS NUMBER, but that does not seem to work. I've uploaded an example, AO is Approved Opportunities, we're trying to sum the Active & Inactive Approved Opportunities to have a total number of Approved Opportunities we've processed. Here's what we have now...... ="Approved Opportunities" + SUMIF([DCAPS Opportunities]50:[DCAPS Opportunities]61, ISNUMBER))
Any insight would be great!
Dan
-
嗨,丹,
It seems like you'll need to add a column for ISNUMBER in your sheet, because it won't accept a range and does require parameters. So adding the column (NumYesNo, for example) would allow you to do this:
="Approved Opportunities" + SUMIF([NumYesNo]50:[NumYesNo]61,TRUE,[DCAPS Opportunities]50:[DCAPS Opportunities]61)
Effectively, the third SUMIF parameter indicates the range from which the values should be pulled, but only if they are numbers based on the value in the NumYesNo column.
Then, in your NumYesNo column on row 50, use the formula
=ISNUMBER([DCAPS Opportunities]50)
Repeat this through to 61.
Let me know how it goes.
-
Thanks Jim,
I've moved around a few rows and cells, but still have same structure. Whereas "Approved Opportunities" is now "Pipeline Summary"
="Pipeline Summary" + SUMIF([NumYesNo]11:[NumYesNo]15,TRUE,[DCAPS Opportunities]11:[DCAPS Opportunities]15)
I'm getting an #INVALID COLUMN VALUE return on the NumYesNo column cell.
Please see attached.
I've tried using the sumif & range replaced by children..but no result.
Thoughts?
Thanks,
Dan
-
Sorry, I just checked and got the same result. Try making the NumYesNo column a checkbox column and see if that works.
-
J. Craig Williams ✭✭✭✭✭✭Can you clarify what you are trying to do? Are you trying to COUNT or SUM the results?
This might be what you are looking for:
="A" + COUNTIFS([YOURCOLUMNNAME]41:[YOURCOLUMNNAME]50, ISNUMBER(@cell))
for rows 41 to 50.
Craig
(Note: I used COUNTIFS instead of COUNTIF out of habit)
-
Craig,
Trying to SUM the results of the children.
Would SUMIF work? And would I have to separate the numbers within the cell from any text or characters?
Thanks,
Dan
-
J. Craig Williams ✭✭✭✭✭✭
Are you trying to SUM the results in the () of the children?
How do you get the numbers in the ()?
I assumed your were trying to reproduce a sum of those numbers in the () and those were counts
Craig
-
Jim,
That doesn't seem to work.
="Pipeline Summary" + SUMIF(NumYesNo9:NumYesNo14, 1, [DCAPS Opportunities]9:[DCAPS Opportunities]14)
Returns. Pipeline Summary 0
Thanks,
Dan
-
I'm trying to do something similar,
在创建任务列表我规范ally use the "Project" option as it's easier to link impact from multiple sheets when estimated duration and seeing change impact.
I'm also creating new start and finish columns because I can apply formulas to them which I can't do within the standard "Project" sheet.
我想remove the d from the duration columns examples
Task name abc - Duration 1d
Task name acb - Duration 12d
What I'm looking to do is add the duration to the new Finish column. I need to remove the d to achieve this any tips would be great
-
I ended up creating a new column to provide just the number using the following formula
=SUBSTITUTE(Duration6, "d", "", 1)
-
Going back to the post on "How to Sum Cells with Numbers & Text" from May 22, 2017 - I am trying to do something similar where I have text and numbers in the same cell but would like to sum up the numbers only in the total column. Is there a way to sum up numbers while ignoring the text?
Thank you!
