Pivot table with multiple rows against the same column headers

[bocgaroe]

Hi experts,

I am trying to create a matrix Pivot table but can't seem to be able to get it to work the way I want it to. I can only get so far, so either I'm just not doing it right or there is no way of doing what I want to do.

I have survey data which I want to analyse using Pivot tables for ease.
There are ten questions, answered on a 1-5 scale, and I want to get the count of each score for each question.
I would then like to add a slicer to allow me to filter against site and/or function.

Attached is some sample data, a tab with the Pivot as far as I could get it (but as you can see, the count for the second question is wrong) and a tab which shows how I'd like to be able to access the data.

Would appreciate your help!

Thanks

[Roel Jongman]

Your data is not that suitable for pivoting because each question is a column with results

But with countifs it's not that hard to build. see the attachement with my solution
I build 2 tables 1 with overall results and 1 where you can set function and/or site

The data set also has some results where function and site are left blank, these to not come thru if you select "All"
But results per function and/or site look solid..

I created to dropdown lists for which the inputvalues are on the data sheet
Have a look if this will fit your needs.

[stasinek]

Hello,
I did not look at my colleague's solution. My proposition is based on the use of Power Query. Pivot table is based directly on the PQ query