Multiple IF Statements with Multiple Columns

mraspie · ‎Jul 29, 2024

I am trying to group line items based on the below conditions, but for some reason it's not working for all the "Done" ones. It does work for the 2023 items that are marked Done in the status column, but the one 2024 item that is marked Done in the status column still returns the 2024Q3. I really hope this makes sense.

IF(REGEX_MATCH({Labels},"(2024Q4_\\w)"),"Unscheduled",

IF(REGEX_MATCH({Labels},"(2024Q4)"),"2024Q4",

IF(REGEX_MATCH({Labels},"(2024Q3)"),"2024Q3",

IF(REGEX_MATCH({Labels},"(2024Q2)"),"2024Q2",

IF(REGEX_MATCH({Labels},"(2024Q1)"),"2024Q1",

IF(AND({Labels},"(2023)",{Status} = "Done"),"2023 Complete",

IF(AND({Labels},"(2024)",{Status} = "Done"),"2024 Complete",

"Unscheduled")))))))

TheTimeSavingCo · ‎Jul 29, 2024

Could you provide a screenshot of some example data and include what you would want the formula's output to be for each piece of data?

Adam, Consultant @ The Time Saving Co

mraspie · ‎Jul 30, 2024

Thanks for the reply! First screenshot, it works as expected. But the 2nd one (same Airtable), it doesn't

The "2024Q3" in the purple column should be "2024 Complete" not "2024Q3".

Also, not sure if it matters but the Labels & Status are being pulled in from a Jira sync.

Alexey_Gusev · ‎Jul 30, 2024

Hi,
it stops when it find true condition. so, labels with 2024Q.. cannot show 'Completed', regardless of Status.
Also, part with AND( ) used in a wrong way. It should be AND (exp1 , exp2 , exp3) and it's true when all three are true.

AND({Labels},"(2024)",{Status} = "Done") means AND ( value of Labels_not_empty , strange_nonempty_string , Status=Done ). when you have nonempty string, but you need boolean value, it just converts nonempty string to TRUE. So, any Labels where 2024Q.. not included and Status=Done will show '2023 Complete',
"'2024 Complete" cannot be shown.

mraspie · ‎Jul 30, 2024

So basically what I want to do can't be done... 😞

TheTimeSavingCo · ‎Jul 30, 2024

Hmm, yeah, I fixed just the 2023/2024 complete part:

IF(
  AND(
      FIND("2024", Labels),
      {Status} = "Done"
    ),
    "2024 Complete",

  IF(
    AND(
      FIND("2023", Labels),
      {Status} = "Done"
    ),
    "2023 Complete"
  )
)

To get around the "2024 Complete" thing not showing I swapped out the order of the conditionals as it seems like you want the latest year to be shown only
---
I don't think your "REGEX_MATCH" stuff works either actually. I would highly recommend reviewing the formula field reference (https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference) as well as @Alexey_Gusev's notes on how your formula could be improved

Adam, Consultant @ The Time Saving Co

mraspie · ‎Jul 31, 2024

Thank you so much! I am still learning how to use formulas so this is really helpful. 🙂

Alexey_Gusev · ‎Jul 31, 2024

No, you just have to change statements order (put Status check at start) and correct the part with AND.
I couldn't show correct formula yesterday, because to be honest I didn't understand what's your goal.
But now I'm think I got it.
In nested IFs, it's better to put 'guard clauses' at start.

IF({Status} = "Done",
  IF( FIND("2024", Labels),"2024 Complete",
  IF( FIND("2023", Labels),"2023 Complete") , 
 ...here you should put 'REGEX_MATCHes' in the same order as in your first formula..
..and put correct number of close brackets...

mraspie · ‎Jul 31, 2024

Like this?

IF({Status} = "Done",

IF( FIND("2024", Labels),"2024 Complete",

IF( FIND("2023", Labels),"2023 Complete"),