I'm trying to create a worksheet that will help us analyze the results
of a satisfaction survey.

Our data is structured in the following way:

--------------------------------------------------------
|QUARTER GENDER OCCUPATION SATISFACTION
--------------------------------------------------------
|2006Q1 MALE STUDENT 5
|2006Q1 MALE RETIRED 4
|2006Q1 FEMALE COMPUTERS 2
|2006Q2 MALE STUDENT 1
|2006Q2 FEMALE STUDENT 5
|2006Q2 FEMALE COMPUTERS 5
--------------------------------------------------------

We have a couple dozen columns, one for each question in our survey
(for simplicity, I'm using just three questions in this example).


I'm looking to create a summary (preferably in a pivot table) that will
show the percentage of respondents with a satisfaction score of either
4 or 5, broken out by the responses to either the gender or occupation
questions (using a List Box form to determine which question is
analyzed). The summary table should look something like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 100% 0%
|FEMALE 0% 50%
-------------------------

or

--------------------------------
|OCCUPATION 2006Q1 2006Q2
--------------------------------
|STUDENT 100% 50%
|RETIRED 100% 0%
|COMPUTERS 0% 0%
--------------------------------


Using this analysis, we'd be able to say things like "it looks like
people in the computers industry are consistently not satisfied with
our product" or "it looks like we've improved our sanctification scores
with females".


What I already have is a List Box form with each question in our
survey. Each time the user selects a new question from the list, I
have VBA instructions to change the PivotField orientation of a simple
Pivot Table so that it gives me a breakout of each response (in the
rows) while keeping it broken out by quarter (in the columns). A Pivot
Chart is then automatically updated, illustrating how our customer base
is changing over time (we can see that more females took our survey
this quarter than last).

The VBA code for that looks like:

Sheets("sheet3").PivotTables("pt1").ColumnFields(1 ).Orientation =
xlHidden
Sheets("sheet3").PivotTables("pt1").PivotFields(Ra nge("questions").Cells(Sheets("Sheet2").Shapes("ro w_attribute").ControlFormat.Value).Value).Orientat ion
= xlColumnField


and if a user selects "GENDER" from the List Box, the resulting Pivot
Table looks like:

-------------------------
|GENDER 2006Q1 2006Q2
-------------------------
|MALE 2 1
|FEMALE 1 2
-------------------------


I'd love to use pivot tables for a similarly elegant solution, but have
been unable to come up with something great in two+ days of work. I'm
looking at an alternative method using array formulas to count rows
that satisfy multiple conditions (such as quarter = "2006Q1", gender =
"female", satisfaction = "4 or 5") using a formula like:

=SUM((raw!$B$7:$B$588="2006Q1")*(raw!$W$7:$W$588=" female")*((raw!$U$7:$U$588=4)+(raw!$U$7:$U$588=5)) )

but it would be messy and not as elegant or simple to maintain as I'd
like.


I hope I've communicated the question clearly (hopefully the spacing
for the tables is readable) and I would be unbelievably grateful if
someone had feedback on this problem.


Thanks so much!
Alon