Suppose I have the following Excel data:
+------+-------+--------+
| Item | User | Status |
+------+-------+--------+
| MTB | Bob | A |
| QCD | Bob | A |
| MTB | Alice | B |
| DFXR | Carol | A |
| QCD | Carol | B |
| MTB | Carol | B |
| MTB | Carol | A |
| DFXR | Carol | B |
+------+-------+--------+
I can pivot this data to get a count of items per user by item type, by putting User
in the Row Labels
box, Item
in the Column Labels
box, and Count of Item
in the Values
box. This gives the following pivot:
+-------------+------+-----+-----+-------------+
| Row Labels | DFXR | MTB | QCD | Grand Total |
+-------------+------+-----+-----+-------------+
| Bob | | 1 | 1 | 2 |
| Alice | | 1 | | 1 |
| Carol | 2 | 2 | 1 | 5 |
| Grand Total | 2 | 4 | 2 | 8 |
+-------------+------+-----+-----+-------------+
I want to create a pivot that will make two separate sets of Count of Item
columns: one for Status="A" and the other for Status="B". It would look like this:
+-------------+----------+---------+---------+----------+---------+---------+-------------+
| Row Labels | DFXR (A) | MTB (A) | QCD (A) | DFXR (B) | MTB (B) | QCD (B) | Grand Total |
+-------------+----------+---------+---------+----------+---------+---------+-------------+
| Bob | | 1 | 1 | | | | 2 |
| Alice | | | | | 1 | | 1 |
| Carol | 1 | 1 | | 1 | 1 | 1 | 5 |
| Grand Total | 1 | 2 | 1 | 1 | 2 | 1 | 8 |
+-------------+----------+---------+---------+----------+---------+---------+-------------+
I'm flexible about how the column names would appear; the important thing is that the counts of Item
get broken out into two sets based on Status
. How would I do this? Can it be done with a pivot table?