Formula to return data based on multiple fields?

[Dakota_Bushnell]

Hello fellow Airtablers!

I’m in a bit of a bind trying to put together a formula for what I’m trying to do here.

I have 3 different single select fields, which based on if they meet certain criteria, will populate another formula field that’ll show “Complete” or “Incomplete”. That formula is working wonderfully. However, I’m trying to come up with a formula that’ll state the reason as to Why it’s showing as incomplete.

I can sort of get it to work for one field, but not multiple.

Here is the formula I have for the field that populates with “Complete” or “Incomplete”:

IF(AND(Status = "Done", {Billing Status} = "Payment Received", {E-file Status} = "Accepted", {Billed}!=BLANK()), "✔ Complete", "❌ Incomplete")

Here’s an image of what it looks like:

As you can see, it’s marked as Incomplete, because the billing status is not marked as “Payment Received”.

I’m attempting to create a column after the {Final Status} column that will show why it’s marked “Incomplete”. Is this possible?

Any help or suggestions are greatly appreciated. I’m still a bit new to Airtable and am trying to learn as much as I can.

Thanks in advance!

[Jeremy_Oglesby · Accepted solution]

I think this will get you what you are looking for:

IF({Status} != "Done", {Status}) &
IF(
  AND(
    {Status} != "Done",
    OR(
      {Billing Status} != "Payment Received",
      {Billed} = BLANK(),
      {E-file Status} != "Accepted"
    )
  ),
  " || "
) &
IF({Billing Status} != "Payment Received", {Billing Status}) &
IF(
  AND(
    {Billing Status} != "Payment Received",
    OR(
      {Billed} = BLANK(),
      {E-file Status} != "Accepted"
    )
  ),
  " || "
) &
IF({Billed} = BLANK(), "Missing Billed Amount") &
IF(
  AND(
    {Billed} = BLANK(),
    {E-file Status} != "Accepted"
  ),
  " || "
) &
IF({E-file Status} != "Accepted", {E-file Status})

[Dakota_Bushnell]

I played around with the IF(OR() sections and came up with something quite similar, but couldn’t figure out exactly where I went wrong. I’ll be referring back to your examples for learning references. Thank you very much for your help, Jeremy! This isn’t the first time. I’ll get the hang of formulas for Airtable eventually.