why does smartsheets not Sum Cells with leading zero values
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 = 92019
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
Comments

Correction  I fat fingered Prev Yr/Mth Yr it is = 92019

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
Help Article Resources
Categories
Check out the Formula Handbook template!