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
- Other questions
- Three nested if statements - not working

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

0
1592
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 23, 2019 12:07 PM

I am trying to get a singular text result depending on what date fields have been entered. I can get each one to work separately with the IF(AND statement but I can’t get all three to work as one formula?

Here are the three formulas I have so far:

IF(AND({To Underwriting},{Resubmitted}),“Resubmitted”,“Submitted”)

IF(AND({To Underwriting},{Initial Approval}),“Approved”)

IF(AND({To Underwriting},{Clear to Close}),“Clear to Close”)

Thank you in advance!

5 Replies 5

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

Jun 23, 2019 12:35 PM

Because `{To Underwriting}`

is common among all three formulas, you can start by checking for that, then check for the others in turn.

```
IF(
{To Underwriting},
IF(
{Clear to Close},
"Clear to Close",
IF(
{Initial Approval},
"Approved",
IF(
{Resubmitted},
"Resubmitted",
"Submitted"
)
)
)
)
```

Reply

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

Jun 23, 2019 10:34 PM

Thanks Justin…greatly appreciate the help!

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

Jun 25, 2019 03:37 AM

I’m try to write the following IF statement in Airtable to eventually make a completion rate chart.

If Date Test Complete is filled in, return 3, If they asked for a deadline extension and the sum of the sections is equal to or greater than 1, return 2, If they asked for a deadline extension but sum of the sections is less than 1 (meaning they didn’t take it), return 1. If they haven’t don’t anything, return 0.

**This is how I’ve attempted writing it. **

IF({Date Test Complete},3, IF({Deadline Extended}, {# test sections}>=1,2,1))

IF({Date Test Complete},3, IF({Deadline Extended},{# test sections}>=1,2, IF({Deadline Extended},{# test sections}<1,1, BLANK())))

Any help would be great! IF statements are not my greatest strength :upside_down_face:

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

Jun 25, 2019 05:27 AM

Your second version is closer to what it should be…well, what it *could* be. There are multiple ways to do it, some more optimized/streamlined than others.

Let’s address this first. Any time you want a field to default to 0 or remain empty or anything equivalent to “nothing” if the rest of your comparisons are false, you don’t actually have to put `BLANK()`

as the last part of the `IF`

function. You can just leave it out entirely. So instead of something like:

`IF({Date Complete}, "Complete", BLANK())`

…all you actually need is:

`IF({Date Complete}, "Complete")`

This works for any field type. Airtable will determine the proper nothingness to include if nothing else is true.

Next, any time you want an `IF`

function to be true based on multiple comparisons, those comparisons need to be wrapped inside an `AND`

function. `AND`

simply takes any number of comparisons or field checks, and only returns true if all of them are true. Its counterpart, `OR`

, will return true if any single comparison inside it is true.

To use `AND`

(and the `BLANK()`

simplification) on your second formula above, the result would be this:

`IF({Date Test Complete},3, IF(AND({Deadline Extended},{# test sections}>=1),2, IF(AND({Deadline Extended},{# test sections}<1),1)))`

However, this can be simplified even further. Because both of your second checks look for something in `{Deadline Extended}`

, you can do a single check for that first, and only test the value in `{# of test sections}`

if the deadline has been extended. And once that value has been tested to see if it’s >= 1, there’s no need for a second check to see if it’s < 1, so we end up with something closer to your first attempt:

`IF({Date Test Complete},3, IF({Deadline Extended}, IF({# test sections}>=1,2,1)))`

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

Jun 25, 2019 08:14 AM