Sum formula not working

Options
Donna B.
Donna B. ✭✭
edited 10/18/22 in Formulas and Functions

I want to sum all the values in the yellow columns, however it's not happening with the usual sum function🤷‍♀️

The values in the yellow columns are all from calculations of IF(AND()) formula, as below:

=IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, "") + IF(AND([Price Type]@row = "Early Bird", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1500, "") + ...

And the sum formula is just a simple:

=SUM([23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row)

I have been using these formula before, but this is the 1st time i encountered this issue. I need a hand :)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide screenshots of the formulas within the sheet similar to the screenshot below?


  • Donna B.
    Options

    Hi @Paul Newcome:


    If I select individually (not a range), it gives me this:


  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    It is interpreting the cells as String and not there value.

    Try this:

    Chage the "IF " satatement. Instead of

    IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, "") +....

    to

    IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, 0) +...


    I think that the summation refuse to add empty strings with integer.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Since you want those blank ones left blank, I would remove the third portion of each IF statement altogether.

    =IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800) + IF(AND([Price Type]@row = "Early Bird", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1500) + ...


    This will leave the cell empty (as opposed to a blank being treated as a text value).

    Another option would be to adjust your SUM formula to a SUMIFS to only pull in cells where there is an actual number value:

    =SUMIFS(range, range, ISNUMBER(@cell)

    =SUMIFS([23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, [23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, ISNUMBER(@cell))

  • Donna B.
    Options

    Hi @Christian G. & @Paul Newcome ,

    Thanks for your response. I did play around with the formula. If I put "(...,1800,0)" this would give 0 values like 0080000 and leave the blanks with varying number of zeros. (sorry forgot to screenshot).

    If I remove entirely the third portion, this still leaves zeros in the blank cell

    But this is better than where I started, but aesthetically I prefer to leave the zero cells blank😅. Might there be a way to do this?

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    In your Gigantic "If" summation, is it possible that only one of them is true at a time?

    If so, I would build a Price Reference Table like so:

    And then only refere the price in a single IF

    =If([23rd Total Radiology 30 jan - 2 feb]@row=1,Index({PriceRef},Match([Price Type]@row,{PriceTypeRef,0)))

    It is not directly solving your issue, but it is still improvment.

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    I think I found why it is originaly not working.

    Let's evaluate the formula.

    =IF(AND([Price Type]@row="Regular",[23rd Total Radiology 30 jan - 2 feb]@row=1)1800,"")+IF(AND([Price Type]@row="Early Bird",[23rd Total Radiology 30 jan - 2 feb]@row=1)1500,"")+IF(AND([Price Type]@row="SP1",[23rd Total Radiology 30 jan - 2 feb]@row=1)1000,"")+IF(AND([Price Type]@row="SP2",[23rd Total Radiology 30 jan - 2 feb]@row=1)1100,"")+IF(AND([Price Type]@row="SP4",[23rd Total Radiology 30 jan - 2 feb]@row=1)1300,"")+IF(AND([Price Type]@row="FOC",[23rd Total Radiology 30 jan - 2 feb]@row=1),0,"")

    Once each IFs are evaluated, we get this:

    =""+1500+""+""+""+"" ==> _1500__ where "_" is ""

    This is not a number for Smartsheet.

    I do think now that my solution with a reference table will solve the issue

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Did you try the SUMIFS to only pull in numbers?

    =SUMIFS(range, range, ISNUMBER(@cell)

    =SUMIFS([23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, [23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, ISNUMBER(@cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!