why does smartsheets not Sum Cells with leading zero values

bday2329 · 10/31/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)

Does SS not add values with leading zeros?

What am I doing wrong?

Thank you in advance for the appreciated help

bday2329 · 10/31/19

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

bday2329 · 10/31/19

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

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

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 · 11/01/19

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

why does smartsheets not Sum Cells with leading zero values

Comments

Help Article Resources

Categories