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