title: Excel pivot table that counts non-numeric data?
body: <p>I'm helping a coworker with a problem and I've run into a bit of a wall myself. He receives a CSV file from a survey of student responses after a set of classes are completed, and he wants to generate reports showing the number of responses in each category in each question (i.e. # Agree, # Strongly Agree, etc). </p>
  
  <p>The CSV file has a format similar to this:</p>
  
  <pre><code>DateTime     School     Class     Question 1        Question 2        Question 3     ... etc ...
  ========     ======     =====     ==========        ==========        ==========
  1/1/2012       A          1       Agree             Strongly Agree    Disagree
  1/1/2012       A          1       Disagree          Agree             Strongly Disagree
  1/1/2012       A          2       Agree             Strongly Agree    Slightly Disagree
  1/1/2012       A          1       Agree             Agree             Disagree
  1/1/2012       A          2       Disagree          Disagree          Disagree
  ... etc 8,000 rows ...
  </code></pre>
  
  <p>What he would like is a report that looks similar to this:</p>
  
  <pre><code>School     Class     Q1 Agree     Q1 Disagree    ...    Q1 Strongly Agree  ...
  ======     =====     ========     ===========          =================
    A          1          2             1                         0
    A          2          1             1                         0
  ... etc ...
  </code></pre>
  
  <p>Obviously I'm looking at a pivot table, but I'm running into an issue with how to define the pivot table. I'm not an expert at Excel, but when we tried various options we came up with essentially non-sensical results.</p>
  
  <p>Is it possible to set up a pivot table that will give this data in a meaningful way? Counting instances of non-numeric values and grouping the counts by school and class? What would be a good (easily repeatable) strategy to accomplish this?</p>
  
  <p>Thanks for any help, much appreciated.</p>
  
  <p>(We are using Office 2007)</p>