Solved

Multiple conditional IF statements?

Forum|Forum|1 year ago
May 29, 2024
5 replies
57 views

E

+4

edge
Participating Frequently

Hi Community, this is a little brainteaser for advanced formula wizards. I am stuck

Here is the situation:

Up to 3 "quality checks" need to be done for some workflow.

Each "quality check" has a check box field

QC1

QC2

QC3

Each quality check also has a single select status field "In Progress", "Review" or "Done".

I want to show in a cell a quick overview of what quality check is still outstanding and if all are DONE it should show "ALL CHECKED"

- If a quality check "QC A" is required AND QCA STATUS is NOT done - it shows the letter: "A" (to signal that QC A still needs to be done)

additionally to the letter A possibly shown:
- If a quality check "QC B" is required AND QC B STATUS is NOT done - it shows the letter: "B"

additionally to the letter A possibly shown:
- If a quality check "QC C" is required AND still outstanding (not done) it shows the letter: "C"

Once all REQUIRED Quality checks are marked DONE, the A, B and C (if required) should dissapear/not show anymore and instead the cell should show the text "ALL CHECKED"

So the cell can have the following results:

A

AB

ABC

AC

BC

or

ALL DONE

What is the formula for this? Please help me! Thanks in advance

Best answer by TheTimeSavingCo

Try this:

IF(
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
  = "",
  "All Checked",
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
)

Link to base

+31

TheTimeSavingCo
Brainy
Answer
Forum|Forum|1 year ago
May 30, 2024

Try this:

IF(
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
  = "",
  "All Checked",
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
)

Link to base

Adam, Airtable Consultant - thetimesaving.company

Like

E

+4

edge
Author
Participating Frequently
Forum|Forum|1 year ago
May 30, 2024

Wow, I am so impressed. It works exactly how i wanted it. You are a star! Thank you!! Greetings from Vienna, Austria 🙂

Like

E

+4

edge
Author
Participating Frequently
Forum|Forum|1 year ago
September 23, 2024

@TheTimeSavingCo Hi, i noticed a glitch in the formula. If no QC is required, meaning all are blank, then by default the formula generates "all checked" but it should be blank if no QC is required. see here:

Like

+31

TheTimeSavingCo
Brainy
Forum|Forum|1 year ago
September 24, 2024

@TheTimeSavingCo Hi, i noticed a glitch in the formula. If no QC is required, meaning all are blank, then by default the formula generates "all checked" but it should be blank if no QC is required. see here:

Sure, try:

IF(
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
  = "",
  IF(
    AND(
      {QC A} = 0,
      {QC B} = 0,
      {QC C} = 0
    ),
    "",
    "All Checked"
  ),
  IF(
    AND(
      {QC A},
      {QC A Status} != "Done"
    ),
    'A'
  ) &
  IF(
    AND(
      {QC B},
      {QC B Status} != "Done"
    ),
    'B'
  ) &
    IF(
    AND(
      {QC C},
      {QC C Status} != "Done"
    ),
    'C'
  )
)

Adam, Airtable Consultant - thetimesaving.company