why does smartsheets not Sum Cells with leading zero values

Options
✭✭✭✭
edited 12/09/19

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)

What am I doing wrong?

Thank you in advance for the appreciated help

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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)))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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)))

• ✭✭✭✭✭✭
Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!