Simple Sum formula not working
I'm running into an issue where it appears the formulas that I have returning a number are not available for simple math in sum formulas. Any suggestions?
For example, I have a column that produces the month a project will close based upon the estimated completion date currently linking in from the individual task lists. I have another column returning the month as a number, and am trying to subtract this from 12 and keep receiving "invalid operation".
Here is the formula I have running for the month to number conversion
=IFERROR(IF([Month Closing]@row = "Jan", "1", IF([Month Closing]@row = "Feb", "2", IF([Month Closing]@row = "Mar", "3", IF([Month Closing]@row = "Apl", "4", IF([Month Closing]@row = "May", "5", IF([Month Closing]@row = "Jun", "6", IF([Month Closing]@row = "Jul", "7", IF([Month Closing]@row = "Aug", "8", IF([Month Closing]@row = "Sep", "9", IF([Month Closing]@row = "Oct", "10", IF([Month Closing]@row = "Nov", "11", IF([Month Closing]@row = "Dec", "12", "")))))))))))), "please check")
and the formula that should be simple but apparently hates me:
=IFERROR(SUM(12-[Start Month]@row),"please check")
Comments
-
Try removing the sum function. The sum function only adds values that are separated by a comma. Simple math can be done without it.
Try this formula instead.
=IFERROR(12-[Start Month]@row, "please check")
-
The other problem is the quotes around your numbers in your IF statement.
11 will return the number 11.
"11" will be displayed the same but is treated as a text string. Since text can't be summed, it will return an error.
-
Good catch Paul! Yes, If you are trying to return a number value, never use quotes!!!
-
Were you able to get this working?
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!