Pivot table help - survey

Hello there, hope someone can help.

I am helping my wife in collating the data on some surveys she created for work. She is a designer of chukdrens clothes and created some activit sheets asking kids about their favorite colors etc.

For instance, one of these asks the questions:

What is your age?
What is your favorite Sport? (Chosen from a list)
What is your favorite Food? (Chosen from a list)
What is your favorite Color? (Chosen from a list)
What is your favorite Print? (Chosen from a list)

I created a spreadsheet with drop down boxes for data entry, except age which was inputted manually. We have all the data inputted and I am now trying to make a pivot table with meaningful data. I would like to use the categories of 'sport, food, print, color' as my report filter - so I only need one table to show all my information. I cant quite seem to get this to work. In the end we would like to show that 'x amount of x year olds chose x as their favorite sport" etc.

I place age into my row field (i will group these by age range later) but would like to choose 'SPORT' in my report filter and have the table show the sports (football, basketball etc) in the columns, with the count below per age. I hope this is clear! Here is an example of the data:

AGE SPORT FOOD PRINT COLOR
5 FOOTBALL BANANA CAMO ORANGE
3 FOOTBALL HAMBURGER DINOSAUR LIGHT BLUE
9 SOCCER PIZZA CAMO TURQUOISE
9 BASEBALL BANANA STRIPES NAVY
6 HOCKEY CHOCOLATE DINOSAUR ORANGE
8 BASEBALL PIZZA FLAGS LIGHT GREY
5 BASKETBALL ICE CREAM CAMO GREEN
4 BASEBALL ICE CREAM PIRATE TURQUOISE
5 HOCKEY HAMBURGER JET PLANES YELLOW
7 BASKETBALL HOT DOG DINOSAUR YELLOW
8 BASEBALL ICE CREAM JET PLANES TURQUOISE
6 BASEBALL BANANA TIE DYE MEDIUM BLUE
3 BASKETBALL CHOCOLATE STRIPES MINT

Hi,

I have put together something that will hopefully be close to what it is that you are asking for. Please let me know what you want changed and I'll see what I can do for you.

By filtering one of the categories in the report filter, you can see how many children of each age chose whatever you filtered.

Hope this helps

Hi!

Thanks so much for your help!

Is it possible to have the categories in the filter? i.e. so i can choose between 'Sport' 'Food' or 'Print', and then table would then show the ages in the row and the type of sport, or type of food, etc in the columns, with the values below?

for instance, i would choose food and get the following:

Food (filter)

age pizza chocolate ice cream

2 1 0 2
4 0 3 4
5 1 6 3

Thanks for your help, attached is a file.

As you can see I have made separate pivot tables for each category (sport, print etc)

But what I would ideally like is for all these categories to be in one pivot table, so I can select the category which would cause each sub category to show.

For instance, I would choose 'sport' and the individual sports would appear with the amount chosen per age.

Fantastic, but how would I then change it from Sport to show 'Print' or 'Color' results?

Is there a way I can change the categories from the pivot table itself? Without having to remove/add in the pivot table setup.

So that someone looking at the table could change from looking at food to looking at color for instance?

Ok so I guess a separate pivot table for each category (sport, food etc) is the only way forward.

One final separate question, In another sheet I have a column for the participants age, then they were asked to circle up to 5 of their favorite icons. These were entered under the columns 'icon 1' 'icon 2' 'icon 3' 'icon 4' 'icon 5', then the name of the icon entered below. (see attached picture)

In the pivot table I want to show for instance how many 5 year olds chose the rocket icon. It doesn't matter if it was choice1, or choice 5 - I just want the overall number! Please help

Please see attached file.

Many thanks!

That is fantastic! How would I do that for other sheets with similar layout? I noticed you used the animal field.