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

Find more posts tagged with

Finance

Formulas

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…