Help

Re: Two-Condition IF Formula

Solved
Jump to Solution
5514 2
cancel
Showing results for 
Search instead for 
Did you mean: 

What is the syntax for an IF statement that only generates output if two conditions, in two fields, are met?

Thanks for your help!

1 Solution

Accepted Solutions
Matt_Bush
Airtable Employee
Airtable Employee

To output one value if two conditions are true, and another value otherwise:
IF(AND(Condition-1, Condition-2), Value-If-Both-Are-True, Value-If-Either-Are-False)

See Solution in Thread

14 Replies 14
Simon_Horup_Es1
6 - Interface Innovator
6 - Interface Innovator

https://support.airtable.com/hc/en-us/articles/221564887-Nested-IF-formulas describes in detail how to nest if statements. You could also use an if and an and.

Matt_Bush
Airtable Employee
Airtable Employee

To output one value if two conditions are true, and another value otherwise:
IF(AND(Condition-1, Condition-2), Value-If-Both-Are-True, Value-If-Either-Are-False)

Hmm.

Right now I have a field that creates an underscore if the referenced field contains data. It’s generating a separator for auto-filenaming.

IF(SEGMENT1 = 0, “”,"_")

But it only works when the field that contains data for the second segment of the filename, the one after the underscore, is present.

When the second segment field is empty, I need the first field to return nothing.

Something like (and pardon my french): IF((SEGMENT1 = 0, “”,"_") BUT(IF(SEGMENT2 = Y, “”,"")))

Matt_Bush
Airtable Employee
Airtable Employee

In that case you may need to use OR() to combine two conditions if either are true (used just like AND otherwise), or NOT() to reverse a single condition. Try different combinations of those. For example,

IF(OR(Condition-1, NOT(Condition-2)), Value-1, Value-2)

MAMC
4 - Data Explorer
4 - Data Explorer

Is it possible to create a conditional IF formula to perform a calculation IF a certain condition is met? I have a CHARGES column, a PAYMENTS column and a DISCOUNT column. I want to creat a formula to calculate the discount IF there is a value entered into the PAYMENTS column. Other wise, the formula (CHARGE-PAYMENT) calculates a discount of the full charged amount. Thoughts?

I’m not exactly sure this is what you are intending, but the following does do something kind of like you describe:

IF(
    {Payments},
    ({Charge}*{Discount})-{Payment},
    {Charge}-{Payment}
    )

This assumes {Payments} is, say, a payment made at the time of purchase, which triggers some sort of percentage discount, while {Payment} is a newly received payment. Based on those assumptions, the formula applies the discount, if applicable, and then subtracts the amount of the received payment. If the purchaser does not qualify for the discount, it subtracts the payment from the full charged amount. In either case, the result is the outstanding balance.

If you can’t warp that to your actual needs, let me know, and I’ll try again.

MAMC
4 - Data Explorer
4 - Data Explorer

Thank you for your response! Its close but not quite. The discount is actually a WRITEOFF and will be equal to the CHARGE minus the PAYMENT only IF a payment is made/entered. If there is no payment entered, the writeoff should not be caculated (as it simply calculates the full CHARGE minus a 0 PAYMENT to give me a WRITEOFF amount equal to the orignal CHARGE which is not what I want).

Well, the standard IF() statement structure would look like this:

IF(
    {Payment},
    [What you want if a payment was received],
    [What you want if no payment was received]
    )

(It’s not a multi-condition IF() as the posting initially discussed.)

So essentially you’re looking at this, possibly with a second clause:

IF(
    {Payment},
    {Charge}-{Payment},
    [What you want if no payment was received]
    )

If you don’t want a value when no payment is received, you could just use this:

IF({Payment},{Charge}-{Payment})

Thank you!! I think that will work. I appreciate the help!

Dakota_Bushnell
6 - Interface Innovator
6 - Interface Innovator

I’m looking for something I would consider similar.

I’m trying to create a formula that would grab from certain fields if they have anything entered.

For example:

I have a field called {Important Client Notes} and another called {Important Company Notes}. What I’d like it to do is if it only has client notes, it’ll pull that information, or if it only has company notes, to grab that information, but if it has both, to grab both and separate them with a separator of my choice; perhaps a "; " or " - ".

I’ve tried writing it in possibly 4-5 ways and can’t seem to get it to work for all 3 possible outputs.

Any help would be greatly appreciated.

{Important Client Notes} &
IF(
   AND(
      {Important Client Notes},
      {Important Company Notes}
   ),
   "; " <<whatever separator you want>>
) &
{Important Company Notes}

I think that should do it.

Why was this so difficult for me to write? I swear, I take like 2 weeks off from Airtable and become helpless with writing the formulas. This worked exactly how I wanted it to. Thank you so much, Jeremy! Much appreciated!

The_Hound
4 - Data Explorer
4 - Data Explorer

I am trying to use a series of nested IF statements to then run the appropriate calculation.

It is for a referral program with 5 levels each one of the levels is eligible for 5 different %OFF discounts and each %OFF discount is valid of a specific number of days.

The simple version is:
If discount field = 5% OFF

Then complete the following calculation - DATEADD({Referral Discount Code Issued Date}, 15, ‘days’),

Else if discount field = 10% OFF

Then complete the following calculation - DATEADD({Referral Discount Code Issued Date}, 30, ‘days’),

etc.

Just wondering if anyone can advise on what I might be doing wrong here:

IF({Referred By - Referral Level}=“GIVE 5% OFF & GET 5% OFF”,“DATEADD({Referral Discount Code Issued Date}, 15, ‘days’)”,IF({Referred By - Referral Level}=“GIVE 10% OFF & GET 10% OFF”,“DATEADD({Referral Discount Code Issued Date}, 30, ‘days’)”,IF({Referred By - Referral Level}=“GIVE 15% OFF & GET 15% OFF”,“DATEADD({Referral Discount Code Issued Date}, 45, ‘days’)”,IF({Referred By - Referral Level}=“GIVE 20% OFF & GET 20% OFF”,“DATEADD({Referral Discount Code Issued Date}, 60, ‘days’)”,IF({Referred By - Referral Level}=“GIVE 25% OFF & GET 25% OFF”,“DATEADD({Referral Discount Code Issued Date}, 75, ‘days’)”,"")))))

What I am finding is that the IF the statement is just returning the formula in the field but not the result. I have tried adding a = sign in front of the DATEADD formula, but that just returns the formula as =DATEADD({Referral Discount Code Issued Date}, 15, ‘days’).

Many thanks in advance!

Marc_Chenut
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I have activities that can be booked with or without a private transport.
The price of the transport vary if they are 2, 6 or 12 people.

I am building my price table.
I have a single select column “transport” with 2 options : “Without transport” / With transport".
Then I have 3 lookup field column to grab the price for 4, 6 or 12 people.
Then I have a column 'number of people"
Then for each activities, I have rows from 1 to 12 people.

In my new column, I want t grab the correct price depending IF the client took the transport and IF 'number of people =<4 , grab the column “transport price for 4 people”.
IF people=<6 , grab column “the transport price for 6 people”.
IF people =<12, grab column “the transport price for 12 people”.

Could you help please?
Many thanks !