Simple Sum formula not working

JVaihinger

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")

Mike Wilday

Try removing the sum function. The sum function only adds values that are separated by a comma. Simple math can be done without it. 

• https://help.smartsheet.com/function/sum

Try this formula instead. 

=IFERROR(12-[Start Month]@row, "please check")

Paul Newcome

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.

Mike Wilday

Good catch Paul! Yes, If you are trying to return a number value, never use quotes!!! 

Paul Newcome

Were you able to get this working?