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!!!

Help Article Resources
Categories
Check out the Formula Handbook template!