excel pivot table - multiple columns

6
votes

I'm trying to create a pivot table in Excel using the following data:

The end result I'm trying to come up with (using the pivot) is a summary like this:

However I can't figure out how to get Excel to count / add / consider all columns. I can create a pivot for each individual field/column, but can't figure out how to compile the data together.

1
excelpivot-table
I think your concept is impossible to do with Pivot tables. – CustomX
Either that or a major bug in Excel :\ Thanks for the effort anyway. I know I can do this via a MySQL query -- was hoping to simplify this a bit with Excel, but I guess not. – Jaren
it's strange enough alright :P Does Pynners answer solve your question? – CustomX

1 Answers

2
votes

Forgive the mac excel in the picture but here is how you can set up a pivot table.

Note though that the row labels will reflect only categories that are in the column you put in the row section

enter image description here