Multiple If Statements
Nested formulas and I are not friends.
This works without putting more than one statement in here, what am I doing wrong and with multiple statements?
=IF(OR([How many xyz]@row = "0-3 xyz", "$50-100K")), IF(OR([xyz?]@row = "4-10 xyz", "100-300K")))
Best Answer
-
Let's look at a simple example. Such as a sheet with video types and results for favorites:
Let's take the first Video Type of Movie and write a simple IF statement for it:
=IF([Video Type]@row="Movie", "Forest Gump")
We've only given the TRUE result for IF the Video Type is a Movie we want it to return Forest Gump. We haven't specified what to do if it's not a Movie. Next let's write a simple formula for Cartoon:
=IF([Video Type]@row="Cartoon", "Spongebob")
Again, only giving the TRUE result for if it's a Cartoon. Now let's write one for TV Show:
=IF([Video Type]@row="TV Show", "Game of Thrones")
Now that we know what we want to nest together, let's do that. To nest IF statements, you only give the TRUE result and then you start over with the next IF where the FALSE should be. Such as this:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones")
Some people only give Nested IF statements TRUE values and assume that they're always going to find a match and have a TRUE result. However, to be safe some people add in a double quotes so that if all TRUE results aren't met with any matches it'll just leave the cell empty. To do that you'd add those double quotes at the end like so:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", "")
Additionally, you don't have to close the loop on all your statements with ))). SmartSheet will fill those in for you. So you could just type this into the cell and you'll notice SmartSheet will end with ))) for you.
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", ""
Lastly, for the formula example you provided I don't think you're after an IF formula with an OR function. You're probably after something like this:
=IF([How many xyz]@row = "0-3 xyz", "$50-100K", IF([xyz?]@row = "4-10 xyz", "100-300K"
Then just let SmartSheet put in the )) at the end for you.
Answers
-
Let's look at a simple example. Such as a sheet with video types and results for favorites:
Let's take the first Video Type of Movie and write a simple IF statement for it:
=IF([Video Type]@row="Movie", "Forest Gump")
We've only given the TRUE result for IF the Video Type is a Movie we want it to return Forest Gump. We haven't specified what to do if it's not a Movie. Next let's write a simple formula for Cartoon:
=IF([Video Type]@row="Cartoon", "Spongebob")
Again, only giving the TRUE result for if it's a Cartoon. Now let's write one for TV Show:
=IF([Video Type]@row="TV Show", "Game of Thrones")
Now that we know what we want to nest together, let's do that. To nest IF statements, you only give the TRUE result and then you start over with the next IF where the FALSE should be. Such as this:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones")
Some people only give Nested IF statements TRUE values and assume that they're always going to find a match and have a TRUE result. However, to be safe some people add in a double quotes so that if all TRUE results aren't met with any matches it'll just leave the cell empty. To do that you'd add those double quotes at the end like so:
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", "")
Additionally, you don't have to close the loop on all your statements with ))). SmartSheet will fill those in for you. So you could just type this into the cell and you'll notice SmartSheet will end with ))) for you.
=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", ""
Lastly, for the formula example you provided I don't think you're after an IF formula with an OR function. You're probably after something like this:
=IF([How many xyz]@row = "0-3 xyz", "$50-100K", IF([xyz?]@row = "4-10 xyz", "100-300K"
Then just let SmartSheet put in the )) at the end for you.
-
Thank you! Worked like a charm!!! And thanks for the great explanation.
-
If an "IF" value is referencing a range of values in the formula, what is the syntax? i.e. If between 1-100, then X, If 100-200 then Y
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi all, I'm struggling to get a formula to work that can return the number of projects that start within the next 4 weeks. In the main sheet we have a project type column and a project start date colu…
- Hi all! I know how to do this manually but am hoping someone has a shortcut! I have a sheet with a very long dropdown list of college majors for employees who have signed up for a particular program. …
- New program proposals are submitted via a form and collected on Sheet 1. When approved by a team leader, the row is copied to Sheet 2. I want to capture the date the copied occurred, which I thought w…
