Pivottable: How to use Likert-type Answers as Cols, Questions as Rows, count of Question responses as Values?

[Domoromo]

Let me apologize in advance if I'm doubling up a previous post. I tried searching for similar things and did not come up with anything. I am using Excel 2010 and Windows 7.

I'm trying to set up a pivot table with several row headings: Country, city, site name.

I have a survey with several likert questions Strongly Disagree, Disagree, etc.

The data I currently have is in the following format (the names have been changed to protect the innocent):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Site[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]New York[/TD]
[TD]Statue of Liberty[/TD]
[TD]Agree[/TD]
[TD]Strongly Agree[/TD]
[TD]Disagree[/TD]
[TD]Agree[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]Toronto[/TD]
[TD]McDonalds[/TD]
[TD]Strongly Disagree[/TD]
[TD]Strongly Disagree[/TD]
[TD]Disagree[/TD]
[TD]Disagree[/TD]
[/TR]
</tbody>[/TABLE]

What I want to end up with is the following:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Site[/TD]
[TD]Question[/TD]
[TD]Strongly Disagree[/TD]
[TD]Disagree[/TD]
[TD]Neutral[/TD]
[TD]Agree[/TD]
[TD]Strongly Agree[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]New York[/TD]
[TD]Statue of Liberty[/TD]
[TD]Q1[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[TD]15%[/TD]
[TD]25%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q2[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q3[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q4[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]Toronto[/TD]
[TD]McDonalds[/TD]
[TD]Q1[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q2[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q3[/TD]
[TD]...[/TD]
[TD]....[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q4[/TD]
[TD]...[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

I tried using question 1 for the column header and then the questions as row and values, but then all of the columns for the other questions only count data that have that response in question 1.

Is it possible to hard code the answer choices? And set up some kind of Countif kind of thing?

Caveats:
All of these data are a subset of a much larger data set that will be added to monthly so I am really trying to find a solution that does not involve me changing the format of the original data in any way.

Also, the example above is a smaller version of what I'm trying to do for the sake of illustrating the problem. The reality of the situation is that there is 10 questions and a large number of country/city/site combinations that do not all have data. So creating a table filled with countifs does not seem like the best solution, but if it's the only way I'll go with it.

Thank you very much for any help you can provide. This one has had me stumped for awhile.

 

[shg]

You can use COUNTIFS. You need to post more of the data in the upper table.