2

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?

1 Answer 1

4

This can easily be done with a Pivot Table. All you need to do is set it up correctly:

  1. Select User for your Row Labels
  2. Select Item for your Values
  3. Select Item again for your Column Labels
  4. Select Status for your Column Labels also.

Here's what it will look like:

Pivot with 2 categories

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .