This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

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

Showing results for

- 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
1227
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