Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formula Help (conditional statement)

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1625
5

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 11:18 AM

Hello and thank you in advance for taking a look at my question!

I would like to have a formula that analyzes two columns of data to return a specific amount, or 0.

Column 1: # Nights

Column 2: Cabin Name

If # Nights is greater than or equal to 7, AND Cabin Name is “299” then $1800, if not then $0

If # Nights is greater than or equal to 7, AND Cabin Name is “514” then $1850, if not then $0

I am struggling with the logic of whether the IF statement for cabin name comes first or the # of nights comes first. Also struggling with the IF(AND…) formula - or perhaps I should be using a different formula altogether?

Please help!

Heather

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 01:27 PM

Here’s an adjusted formula that will output 0 if the other conditions aren’t met:

```
IF(
AND(
{# Nights}>7,{Cabin Name}=299
),
1800,
IF(
AND(
{# Nights}>7,{Cabin Name}=514
),
1850,
0
)
)
```

You need to add 0 as the `ELSE`

part of the second conditional statement, and move the closing parenthesis for both statements after that.

Reply

5 Replies 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 11:32 AM

There are a few different ways that you could structure this formula, but here’s one way to do it:

`IF(`

`AND({# nights}>7,{Cabin Name}=299),1800,`

`IF(`

`AND({# nights}>7,{Cabin Name}=514),1850)`

`)`

I’m actually not sure how to get the formula to spit out the value 0 — Airtable removes the number 0 from the formula if you try to add 0 into the formula. So this formula will result in a blank field instead of 0.

Perhaps someone else could chime in on how to get it to spit out the number 0!

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 01:27 PM

Here’s an adjusted formula that will output 0 if the other conditions aren’t met:

```
IF(
AND(
{# Nights}>7,{Cabin Name}=299
),
1800,
IF(
AND(
{# Nights}>7,{Cabin Name}=514
),
1850,
0
)
)
```

You need to add 0 as the `ELSE`

part of the second conditional statement, and move the closing parenthesis for both statements after that.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 01:49 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 04:02 PM

Here are two other possible formulas with a slightly different approach.

These versions use a switch statement instead of nested `IF`

statements, so they are a little more maintainable if you have many more cabins, or if you want to change up the number of nights.

These formulas assume that {Cabin Name} is a single select or single line text field. If it is a number field, just remove the quotes.

Option with nested `SWITCH`

:

```
IF(Nights >= 7,
SWITCH({Cabin Name},
"299", 1800,
"514", 1850,
0
),
0
)
```

Option with nested `IF`

:

```
SWITCH({Cabin Name},
"299", IF(Nights >= 7, 1800, 0),
"514", IF(Nights >= 7, 1850, 0),
0
)
```

If you have the answer to your question, please mark one of the posts as the solution. Otherwise, could you please give a bit more details and a screen capture?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 15, 2020 07:34 PM

Thank you very much. This was perfect, although I am still trying to figure out just HOW it works!

Heather