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
Check out the Formula Handbook template!