0

Ideally, I am looking for a solution where I can see the total number of reasons why the device failed, which is currently recorded in a dropdown list on excel (with option for multiple entries). When using a pivot table I'm only able to see the all the different combinations rather than sole counts of A,B,C etc.... Ideally looking to create options that give this but at each stage of failure (all 5) too.

Currently, the data looks like this:

| Reference Number | Stage of Faliure | Reason For Failure |
| --------         | --------------   | ------------------ |
| First            | 1                | A, B               |
| Second           | 2                | A, C               |

Any ideas appreciated & hope this is enough context to explain the issue.

Ideal goal Currently, the data looks like this:

| Reason for Failure | Total Count | Stage 1 Count | Stage 2 Count |
| --------           | ----------- | ------------  | --------------|
| A                  | 2           | 1             | 1             |
| B                  | 1           | 1             | 0             |
Tom Newton
  • 81
Josh Rawcliffe
  • 1

2 Answers2

1

The key here is that COUNTIF() and COUNTIFS() can accept wildcards in a string comparison.

If your data looks like this:

Data

Then your formulas can look like this:

Formulas

Total Count: =COUNTIFS(C$2:C$20,"*"&E2&"*")

Stage 1 Count: =COUNTIFS(B$2:B$20,1,C$2:C$20,"*"&E2&"*")

Stage 2 Count: =COUNTIFS(B$2:B$20,2,C$2:C$20,"*"&E2&"*")

Just be careful when your settings up the ranges you reference as absolute ($) or relative (no $) so that it'll work the way you want when you copy/paste down.

Engineer Toast
  • 5,218
0

As the variant, it can be done transformation of the initial table

Reference Number | Stage of Faliure | Reason For Failure |
| --------         | --------------   | ------------------ |
| First            | 1                | A, B               |
| Second           | 2                | A, C               |

Particulary, to split the lines with several "Reason For Failure" entries for several separate lines with one of the value, like this

  Reference Number | Stage of Faliure | Reason For Failure |
| --------         | --------------   | ------------------ |
| First            | 1                | A               |
| Second           | 2                | C               | 
| First            | 1                | A               |
| Second           | 2                | A               |
| First            | 1                | B               |
| Second           | 2                | C               |

Then - to made filtering and/or groupping by columns using standard Excel table operations

Tom Newton
  • 81