Sum a column where the value in the column is generated from a formula

I have a column (Column B) that returns a value based on a nested if statement; column A = x% Column B returns a value based on the % entered in column A. Here is the nested if statement:

= IF([Allocation %]@row = 0.05, "2", IF([Allocation %]@row = 0.1, "4", IF([Allocation %]@row = 0.2, "8", IF([Allocation %]@row = 0.3, "12", IF([Allocation %]@row = 0.4, "16", IF([Allocation %]@row = 0.5, "20", IF([Allocation %]@row = 0.6, "24", IF([Allocation %]@row = 0.7, "28", IF([Allocation %]@row = 0.8, "32", IF([Allocation %]@row = 0.9, "36", IF([Allocation %]@row = 1, "40"))))))))))

I am trying to sum the values in Column B and it just returns 0. Column B is column type: text/number

Best Answers

  • Michael Pappas
    Michael Pappas ✭✭✭✭
    Answer ✓

    Right now, it looks like the value that you are producing using the IF statement is being read as text, not as a number.

    Two possible solutions:

    1. Change the nested if statement to = [Allocation %]@row * 40
    2. Put the entire Nested IF statement inside of a VALUE function. => = VALUE(IF(......))
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Whenever you put quotes around a number, you are converting it to a text value. A third option would have been to remove the quotes from around the numbers in the nested IF formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!