PIVOT TABLES- How to count the number of people who answer the same thing for multiple topics-PLEASE HELP ME CRACK IT!

[excellearner18]

Hi,

I am new(ish) to pivot tables and looking for some help please:

We have had a group of people assess how good they are at a number of activities by rating themselves Red, Amber or Green, and I now want to pull together a single table showing how many red, how many Amber and How many green for each topic (with no details on who chose what).

I can do this the long way-"countif" for each, and I can do it separately for each activity using pivot tables- I can only figure out how to create a separate table for each activity, using the activity as the row, which then breaks it down into Red, Amber, Green. However, if I add more activities, it becomes skewed and moves red, green and ambers around and seems gibberish to me! I have tried lots of versions and can't seem to do it!

I would like to create one table which has the activities as row titles, and Red, Amber, and Green as column titles (with the count of each as the data). Is this possible via one pivot? For the raw data, the row titles are Person 1, Person 2, Person 3 etc (though I have also tried to try it without this column at all), and the column titles are the activities. Is this impossible as Red, Amber and Green are not titles, or is there a way around it?

EG of the type of table:

	Activity 1	Activity 2	Activity 3	etc etc
Person 1	green	green	red	green
Person 2	red	amber	amber	red
Person 3	amber	green	green	amber
person 5	green	red	red	red
etc	red	red	green	green
etc	green	amber	green	red

<tbody>
</tbody>

Also, bonus question: if there is not a way around it, why cant I use "=", in a different table to simply get the results from the pivot into a single table?

Thanks so much-Help meee!

 

[ghrain22]

My understanding is your base data looks like what you've provided. But you'll need a column header for the first column
Try this:
1. Alt then D then P
2. Select "Multiple Consolidation ranges" then next
3. "Create a single page for me" > Next
3. Select your Data > Next
4. Select where you want your pivot to be. > Finish

5. Move your "Row" Field to the "Values"
6. Mose your "Value" Field to the "Rows Labels"