Creating a Formula Field That Summarizes the Responses of Multiple Single Select Fields

[epinske]

I am trying to write a formula field that will summarize the responses of three separate single select fields (Prototype 1 Cost Status, Prototype 2 Cost Status, and Prototype 3 Costing Status) so my costing team can have an idea of what proportion of the current prototype stage is "Meeting Target" or "Not Meeting Target". I'm finding this difficult because if we move from Prototype 1 to Prototype 2, we will need the response from Prototype 2 to override the response for Prototype 1 (as shown in the attached png) and so on for Prototype 3. 

This is what I have been able to draft so far: 

IF(

  AND( {P1 Cost Status} = BLANK(),

    {P2 Cost Status} = BLANK(),

    {P2 Cost Status} = BLANK(), BLANK()

IF(

  AND( {P1 Cost Status} = "Green - Meeting or Exceeding Target",

    {P2 Cost Status} = "Green - Meeting or Exceeding Target",

    {P2 Cost Status} = "Green - Meeting or Exceeding Target", "Meeting Target")

IF(

  AND( {P1 Cost Status} = "Orange -1% to -4.99% Variance",

    {P2 Cost Status} = "Orange -1% to -4.99% Variance",

    {P2 Cost Status} = "Orange -1% to -4.99% Variance", "Not Meeting Target")

IF(

  AND( {P1 Cost Status} = "Red -5% or More Variance",

    {P2 Cost Status} = "Red -5% or More Variance",

    {P2 Cost Status} = "Red -5% or More Variance", "Not Meeting Target")

        )

      )

    )

  )

)

Please ask questions if any clarification is needed.

[Mike_AutomaticN · Accepted solution]

Hey @epinske!

I might be missing some context, but please give the following formula a try -maybe a leaner version of it could be achieved.

IF(
  {Prototype 3 Cost Status} = "Green - Meeting or Exceeding Target",
  "Meeting Target",
  IF(
    {Prototype 3 Cost Status} = "Orange -1% to -4.99% Variance",
    "Not Meeting Target",
    IF(
      {Prototype 3 Cost Status} = "Red -5% or More Variance",
      "Not Meeting Target",
      IF(
        {Prototype 2 Cost Status} = "Green - Meeting or Exceeding Target",
        "Meeting Target",
        IF(
          {Prototype 2 Cost Status} = "Orange -1% to -4.99% Variance",
          "Not Meeting Target",
          IF(
            {Prototype 2 Cost Status} = "Red -5% or More Variance",
            "Not Meeting Target",
            IF(
              {Prototype 1 Cost Status} = "Green - Meeting or Exceeding Target",
              "Meeting Target",
              IF(
                {Prototype 1 Cost Status} = "Orange -1% to -4.99% Variance",
                "Not Meeting Target",
                IF(
                  {Prototype 1 Cost Status} = "Red -5% or More Variance",
                  "Not Meeting Target",
                  BLANK()
                )
              )
            )
          )
        )
      )
    )
  )
)

By checking each Prototype Cost Status in the inverse order (meaning from Prototype 3 to Prototype 1), you would be solving the issue I believe. Please let me know if it does!

Best,
Mike, Consultant @ Automatic Nation

[epinske]

Hey @Mike_AutomaticN ,

I appreciate the assistance. Looks like the formula is doing exactly what I need it to do. Also, great tip on inverse order. That will be very helpful for future projects. 

Regards,

Erich 

[Mike_AutomaticN]

Glad to hear that!!