How to Sum Cells with Numbers & Text

Dereck H

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?

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.

CanadaJim

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

DCAPS Inc.

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

CanadaJim

Hi 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.

DCAPS Inc.

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

 

CanadaJim

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)

DCAPS Inc.

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

DCAPS Inc.

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

Joe Borthwick

I'm trying to do something similar,

when creating a task list I normally 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.

I'm looking to 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

 

Joe Borthwick

I ended up creating a new column to provide just the number using the following formula

=SUBSTITUTE(Duration6, "d", "", 1)

PrarthanaP

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!