why does smartsheets not Sum Cells with leading zero values

bday2329bday2329 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
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)

Does SS not add values with leading zeros?

What am I doing wrong?

Thank you in advance for the appreciated help

Comments

  • bday2329bday2329 ✭✭✭✭✭

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

  • bday2329bday2329 ✭✭✭✭✭

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

  • bday2329bday2329 ✭✭✭✭✭

    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 WilliamsJ. Craig Williams Top Contributor

    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 or Register to comment.