excel pivot table - multiple columns

Question

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.

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
– Jaren, Commented Mar 19, 2013 at 23:27
it's strange enough alright :P Does Pynners answer solve your question? — CustomX
– CustomX, Commented Mar 20, 2013 at 8:01

Pynner · Accepted Answer · 2013-03-20 02:46:20Z

2

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

answered Mar 20, 2013 at 2:46

Pynner

1,01710 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jaren Over a year ago

@Pyner Thanks, and the key indicator is what you noted in your second sentence -- only what is in the row label will determine what categories / columns are displayed. That said, the question is if there a way to pivot using multiple rows. I've read some other articles / forums where it was recommended to create an additional "helper" column to do this. However I wasn't able to make any sense of it.

Pynner Over a year ago

To be honest I am not sure that I follow the concept of a helper column in this scenario. Presumably you create a column that has all of the unique values to be selected, and use that as the row label. That said, I am not sure how you could have it auto update for new categories. If the categories are known ahead of time though, it is more simple to us countif formulas instead as you can avoid all the manual refreshing.

Collectives™ on Stack Overflow

excel pivot table - multiple columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related