Pivot Table From Survey Data

[llll93]

Hi all,

Trying to figure out how to create a pivot table with some survey data. See attached for the data and what I want the final table to look like. The 1, 2, 3, 4 etc in the columns are basically "yes" answers and blanks represent "no" answers. I want the first column to be the column headings and the subsequent columns to each be a separate row counting how many "yes" answers in that column. Hopefully that makes sense.

Thanks!

[stasinek]

Hi,
You can easily do this by creating a pivot table using the Pivot Tables Wizard (option - multiple ranges). Do not use the option to insert a pivot table from the Insert tab. Use one data field (result in the PT1 sheet). Then double-click on total sum and create a data source for this PivotTable in the new worksheet where your data will already be modified. On their basis (after a slight correction) you can create a "regular" pivot table (result in the PT2 sheet).

[llll93]

Hi, thanks for your help, however the table is not quite right. I don't see the difference between the tables in PT1 and PT2 and the numbers are off, I guess it's adding the larger numbers instead of counting each as "1"?

[stasinek]

In this case, change the sum to a counter.