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 🙂