why does smartsheets not Sum Cells with leading zero values

bday2329

I am trying to do a =SUMIF into a sheet via cross reference (=SUMIF({Mth/Yr}, ={Prev Yr Mth/Yr}, {DofC NN.NN}))

Mth/Yr = 9-2019

Prev Yr Mth/Yr = 9/2019

DofC NN.NN contains rows that have a value with 0.nn (nn = 01 thru 99)

The result from the SUMIF is 0 (zero)

Does SS not add values with leading zeros?

What am I doing wrong?

Thank you in advance for the appreciated help

Find more posts tagged with

Healthcare

Formulas

Project Management

Comments

bday2329

Correction - I fat fingered Prev Yr/Mth Yr it is = 9-2019

bday2329

Another thing i should mention is for the cells DofC NN.NN they are derived by this formula - =IF([DofC MM]17 >= 10, MID([DofC MM]17, 1, 2) + "." + RIGHT([Duration of Call]17, 2), IF([DofC MM]17 < 10, MID([DofC MM]17, 2, 1) + "." + RIGHT([Duration of Call]17, 2)))

Paul Newcome

It is because your numbers are actually being stored as text.

You would need to convert them to numerical values before trying to sum them.

Try wrapping your DofC NN.NN formula in a VALUE statement.

bday2329

Thanks Paul using this how would you recommend I incorporate the value statement

=IF([DofC MM]17 >= 10, MID([DofC MM]17, 1, 2) + "." + RIGHT([Duration of Call]17, 2), IF([DofC MM]17 < 10, MID([DofC MM]17, 2, 1) + "." + RIGHT([Duration of Call]17, 2)))

J. Craig Williams

Is DoC a time duration? Has it been translated to a percentage (1.75 = 1 hour and 45 minutes)?

Your formula for DoC calc looks funny to me.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…