Help on IF Formula  Multiple Statements
Hi!
I need some help on a IF Formula statement. On my sheet, I have a column that is using this equation 
=IF([Total ALL Athletes and Unified Partners]@row = 0, "0", IF([Total ALL Athletes and Unified Partners]@row <= 5, "1", IF([Total ALL Athletes and Unified Partners]@row <= 74, "2", IF([Total ALL Athletes and Unified Partners]@row <= 149, "3", IF([Total ALL Athletes and Unified Partners]@row <= 224, "4", IF([Total ALL Athletes and Unified Partners]@row <= 299, "5", IF([Total ALL Athletes and Unified Partners]@row <= 374, "6", IF([Total ALL Athletes and Unified Partners]@row <= 450, "7"))))))))
Basically, this formula is calculating a series of IF statements based a total from another column. In my example, the total number of athletes and partners will decide the number of Delegates based on these ranges:
 1 for Delegations between 15 athletes and Unified partners
 2 for Delegations between 674 athletes and Unified partners
 3 for Delegations between 75149 athletes and Unified partners
 4 for Delegations between 150224 athletes and Unified partners
 5 for Delegations between 225299 athletes and Unified partners
 6 for Delegations between 300374 athletes and Unified partners
 7 for Delegations between 375 – 450 athletes and Unified partners
I am having success with this formula and it's giving me the correct numbers. However, at the end of my sheet, where I have an overall total, it is not recognizing the cell. In the photo below, I have 12 Athletes + 2 Delegates (this is the column where my formula is located in) + 4 Coaches + 1 Medical staff + 1 AS Staff. This should equal to 20 total in the delegation, but it is only recognizing 18. I've doubled checked all my SUM equations and everything is correct. There's something wrong in my IF equation that is over riding my other formulas. Any advice or help would be appreciated! 🙂
Best Answer

Hi @Brittany Pair ,
Try:
=IFERROR(IF([Total ALL Athletes and Unified Partners]@row = 0, 0, IF([Total ALL Athletes and Unified Partners]@row <= 5, 1, IF([Total ALL Athletes and Unified Partners]@row <= 74, 2, IF([Total ALL Athletes and Unified Partners]@row <= 149, 3, IF([Total ALL Athletes and Unified Partners]@row <= 224, 4, IF([Total ALL Athletes and Unified Partners]@row <= 299, 5, IF([Total ALL Athletes and Unified Partners]@row <= 374, 6, IF([Total ALL Athletes and Unified Partners]@row <= 450, 7,"")))))))),"")
Placing number in quotes makes them text. I also tweaked it so there is always an answer no matter what's in your [total all athletes...] column.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

Hi @Brittany Pair ,
Try:
=IFERROR(IF([Total ALL Athletes and Unified Partners]@row = 0, 0, IF([Total ALL Athletes and Unified Partners]@row <= 5, 1, IF([Total ALL Athletes and Unified Partners]@row <= 74, 2, IF([Total ALL Athletes and Unified Partners]@row <= 149, 3, IF([Total ALL Athletes and Unified Partners]@row <= 224, 4, IF([Total ALL Athletes and Unified Partners]@row <= 299, 5, IF([Total ALL Athletes and Unified Partners]@row <= 374, 6, IF([Total ALL Athletes and Unified Partners]@row <= 450, 7,"")))))))),"")
Placing number in quotes makes them text. I also tweaked it so there is always an answer no matter what's in your [total all athletes...] column.
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Mark  you are an absolute life saver!! Thank you so so much!

Hi Brittany, Happy to help. Thank you for the vote up and for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!