Sum formula not working

Donna B.

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

Paul Newcome

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

Donna B.

Hi @Paul Newcome:

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

Christian G.

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

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.

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.

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.

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

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