Excel pivot table with many columns header in rows

Question

I have excel data on the following format

Task     Team1  Team2   Team3   Team4 Team5
TaskA       X
TaskA       X    X               X
TaskC                    X               X
taskB       X    X       X       X       X
taskA       X            X
taskB       X

I want to have a pivot table in this format (for each Team, count of Tasks of each type)

         TaskA       taskB         taskC  
Team1       3           2            0
Team2       1           1            0
Team3       1           1            1
Team4       1           1            0
Team5       0           1            1

Any help how I can do that ? (with pivot table or anything else).

Thanks,

1st unpivot the data using the second answer here: https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal . 2nd from the result of the unpivot, pivot the data as desired. — Scott Craner, Nov 20 '18 at 19:39

score 1 · Answer 1 · edited Nov 20 '18 at 19:58

1

I can't embed images, but here is a solution I found. You can use a pivot table.

Select your data source and enter the following pivot table criteria.

Filters: (none)
Columns: "Task"
Rows: "Sum of Values"
Sum of Values: "Count of Team1", "CountofTeam2", etc

[Pivot Table Criteria][1]

Source Data,Pivot Table,Pivot Table Criteria

edited Nov 20 '18 at 19:58

Scott Craner

148,073
10
49
81

answered Nov 20 '18 at 19:43

Aria Dewes

13
3

Excel pivot table with many columns header in rows

1 Answers1