Multiple conditional IF statements?

[edge]

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

[TheTimeSavingCo · Accepted solution]

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

[edge · Accepted solution]

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

[edge]

@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: 

[TheTimeSavingCo]

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

[edge]

@TheTimeSavingCo Adam is a living legend! Wow! Thank you!