Help

IF Formula with values from multiple columns

Topic Labels: Formulas
Solved
Jump to Solution
2060 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Franco_Caporale
4 - Data Explorer
4 - Data Explorer

Hello Everyone, I’m very new with AirTable and I have been struggling with this formula.

I have these 3 different columns: 1) Lead Count, 2) Opportunity Count 3) Total Amount ($). I’m creating a formula to select the Account Status based on these three columns in this way:

  • If Lead Count = 0 --> “Cold”
  • If Lead Count = 1 --> “Aware”
  • If Lead Count >1 --> “Qualified”
    OR
  • If Opportunity Count >0 --> Opportunity (regardless of Lead Count)
  • If Total Amount ($) > 0 --> Customer (regardless of anything else)

How can I put this into a formula?

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @Franco_Caporale - I found the easiest way to do this is to break it down into 4 formula fields. The first 3 are “interim” statuses and the final field is the final status you’re after.

Screenshot 2019-03-28 at 10.18.00.png

Status Lead produces the status of the Lead Count column using this:

SWITCH({Lead Count}, 0, "Cold", 1, "Aware", "Qualified")

Status Opp produces the status of the Opportunity Count column using this:

IF({Opportunity Count} > 0, "Opportunity", "")

Status Total produces the status of the Total Amount column using this:

IF({Total Amount} > 0, "Customer", "")

And, finally, the Status column works off the other 3 to provide the consolidated status using:

IF({Status Total}, {Status Total}, IF({Status Opp}, {Status Opp}, {Status Lead}))

You can hide the 3 interim status columns to make the view a bit prettier.

You could probably combine these into a single formula/column if you wanted to, although in this case, I think separating them makes the formulas easier to manage.

JB

See Solution in Thread

3 Replies 3
JonathanBowen
13 - Mars
13 - Mars

Hi @Franco_Caporale - I found the easiest way to do this is to break it down into 4 formula fields. The first 3 are “interim” statuses and the final field is the final status you’re after.

Screenshot 2019-03-28 at 10.18.00.png

Status Lead produces the status of the Lead Count column using this:

SWITCH({Lead Count}, 0, "Cold", 1, "Aware", "Qualified")

Status Opp produces the status of the Opportunity Count column using this:

IF({Opportunity Count} > 0, "Opportunity", "")

Status Total produces the status of the Total Amount column using this:

IF({Total Amount} > 0, "Customer", "")

And, finally, the Status column works off the other 3 to provide the consolidated status using:

IF({Status Total}, {Status Total}, IF({Status Opp}, {Status Opp}, {Status Lead}))

You can hide the 3 interim status columns to make the view a bit prettier.

You could probably combine these into a single formula/column if you wanted to, although in this case, I think separating them makes the formulas easier to manage.

JB

Also to add, the Lead Count field, which is used in the SWITCH formula, needs a default value of 0. Otherwise, when you create an new record and the lead count number is empty, you get “qualified” in the Status Lead field, which you don’t want.

Franco_Caporale
4 - Data Explorer
4 - Data Explorer

Hi @JonathanBowen, thank you very much for the detailed response! Very appreciated!

This is the perfect approach, I like how more manageable it is, even if we decide to change values, without having to deal with all those nested formulas.

Again, thank you so much, you saved me hours of frustration! :slightly_smiling_face: