Help

Multiple IF Statements with Multiple Columns

Topic Labels: Formulas
1898 12
cancel
Showing results for 
Search instead for 
Did you mean: 
mraspie
4 - Data Explorer
4 - Data Explorer
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")))))))
12 Replies 12

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?

mraspie
4 - Data Explorer
4 - Data Explorer

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

mraspie_1-1722343848971.png

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

mraspie_0-1722343755163.png

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

 

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.

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

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"
  )
)

 

Screenshot 2024-07-31 at 11.13.18 AM.png

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

 
mraspie
4 - Data Explorer
4 - Data Explorer

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

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...

Like this?

 

IF({Status} = "Done",
IF( FIND("2024", Labels),"2024 Complete",
IF( FIND("2023", Labels),"2023 Complete"),
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",
"Unscheduled")))))))

Yes, looks good. 
The only problem is that you need to update the formula each 3 months, enter year 2025 etc.
But you can try to improve it, copy formula to the notepad and do Replace All   2024 => YEAR(TODAY())  , 2023 => (YEAR(TODAY())-1)
Of course it depends on what input data you expect in future, but I hope you understand the idea.