Skip to main content
Solved

IF Formula with values from multiple columns

  • March 28, 2019
  • 3 replies
  • 53 views
F
Forum|alt.badge.img+4

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?

Best answer by JonathanBowen

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.

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

3 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • March 28, 2019

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.

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

H
F
Justin_Barrett
JonathanBowen
Forum|alt.badge.img+18

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.

F
Forum|alt.badge.img+4

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:

JonathanBowen
Terms & ConditionsAccessibility statement