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?
-
-
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))
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!