Group and Count Pivot Table

Question

I'm trying to create a pivot table that counts the number of occurrences of a certain value and also the number of possible occurrences of that value. I'm stuck after adding the case id as a row to the pivot table though. I've never used excel before so a pivot table may be the wrong tool.

Given this is my data on Sheet1:

| CaseId | QId | Validity |
| ------ | --- | -------- |
| 12345  | A1  | Valid    |
| 12345  | A2  | Error    |
| 12345  | A3  | Error    |
| 23456  | A1  | NA       |
| 23456  | A2  | NA       |
| 23456  | A3  | Valid    |

how do I transform that to the following in Sheet2? Errors is the count of validity = Error and possible errors is the count of validity != NA

| CaseId | Errors | Possible Errors |     |
| ------ | ------ | --------------- | --- |
| 12345  | 2      | 3               |     |
| 23456  | 0      | 1               |     |
| Totals | 2      | 4               | 50% |

Answer 1

If you're willing to add a helper column, you can use COUNTIF() to get your summary table.

Starting with your data table :

Add a helper column, which uses CONCATENATE to create a composite index, seperated by a delimiter (I used semicolon here) :

=CONCATENATE(A2,";",C2)

You can then build your summary table :

Calculating the number of Errors for each CaseId would simply be counting the number of times the string 'CaseID;Error' occurs in the INDEX column

=COUNTIF(D:D,CONCATENATE(F2,";Error"))

The number of "Possible Errors" would be the total number of results of that CaseID minus the CaseIds that were 'NA' :

=COUNTIF(A:A,F2)-COUNTIF(D:D,CONCATENATE(F2,";NA"))

The totals should be a simple SUM() formula :)

Hope this helps.