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 = 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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!