or Explore Discussions

# why does smartsheets not Sum Cells with leading zero values

10/31/19 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

Popular Tags:

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

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

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

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

• 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