Sum formula not working
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 30Jan2Feb 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

Are you able to provide screenshots of the formulas within the sheet similar to the screenshot below?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!


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.

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 30Jan2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan  2 Feb Price AED (VAT Ex)]@row, [23rd Total Radiology 30Jan2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan  2 Feb Price AED (VAT Ex)]@row, ISNUMBER(@cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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?

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.

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

Did you try the SUMIFS to only pull in numbers?
=SUMIFS(range, range, ISNUMBER(@cell)
=SUMIFS([23rd Total Radiology 30Jan2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan  2 Feb Price AED (VAT Ex)]@row, [23rd Total Radiology 30Jan2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan  2 Feb Price AED (VAT Ex)]@row, ISNUMBER(@cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 302 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!