Pivottable for Yes, No, N/A Responses - Why is it so convoluted?

[trikky]

I have a table where rows are individual submissions, and columns are questions within those submissions. Submissions contain information such as name, month & location, and questions are answered with either Yes, No or N/A.

	A​	B​	C​	D​	E​	F​	G ​
1​	Name​	Month​	Q1​	Q2​	Q3​	Q4​	Q5​
2​	Fred	Feb	No	No	No	N/A	No
3​	Betty	Jan	No	No	N/A	No	No
4​	Barney	Jan	No	N/A	N/A	N/A	No
5​	Wilma	Jan	No	N/A	N/A	No	N/A
6​	Fred	Jan	Yes	Yes	No	Yes	N/A
7​	Betty	Mar	No	Yes	No	Yes	No
8​	Barney	Mar	Yes	N/A	Yes	Yes	No
9​	Wilma	Feb	No	N/A	N/A	N/A	Yes
10​	Fred	Feb	Yes	No	N/A	No	N/A
11​	Betty	Feb	N/A	Yes	No	Yes	N/A
12​	Barney	Jan	Yes	Yes	Yes	No	N/A

<tbody>
</tbody>

My goal is to get a pivottable that will give me an accurate count of the responses to the questions, that can be drilled down by name and month.

	Q1​	Q2​	Q3​	Q4​	Q5​
Yes	4​	4​	2​	4​	1​
No	6​	3​	4​	4​	5​
N/A	1​	4​	5​	3​	5​

<tbody>
</tbody>

or

	Yes ​	No​	N/A​
Q1​	4​	6​	1​
Q2​	4​	3​	4​
Q3​	2​	4​	5​
Q4​	4​	4​	3​
Q5​	1​	5​	5​

<tbody>
</tbody>

However, no matter the options I’ve tried in the pivottable,I can’t get something that gives accurate numbers. I’ve tried various options in the pivottable layout quadrants, to no avail. I’ve created a helper column with random Yes No or N/A responses to use that as the basis for the title, but without luck.

What am I doing wrong here? All I want is a simple Yes/No count (preferably percentage) for each question, that can be sliced based on the other columns in the source data.

Thanks in advance.

 

[mrshl9898]

To get the Pivot you want, the data would need to be in a format like the below.

Name	Month	Q	y/n/na
Fred	Feb <tbody> </tbody>	Q1 <tbody> </tbody>	No <tbody> </tbody>
Fred	Feb <tbody> </tbody>	Q2 <tbody> </tbody>	No <tbody> </tbody>
Fred	Feb <tbody> </tbody>	Q3 <tbody> </tbody>	No <tbody> </tbody>
Fred	Feb <tbody> </tbody>	Q4 <tbody> </tbody>	#N/A <tbody> </tbody>
Fred	Feb <tbody> </tbody>	Q5 <tbody> </tbody>	No <tbody> </tbody>
Betty <tbody> </tbody>	Jan <tbody> </tbody>	Q1 <tbody> </tbody>	No <tbody> </tbody>
Betty <tbody> </tbody>	Jan <tbody> </tbody>	Q2 <tbody> </tbody>	No <tbody> </tbody>
Betty <tbody> </tbody>	Jan <tbody> </tbody>	Q3 <tbody> </tbody>	#N/A <tbody> </tbody>
Betty <tbody> </tbody>	Jan <tbody> </tbody>	Q4 <tbody> </tbody>	#N/A <tbody> </tbody>
Betty <tbody> </tbody>	Jan <tbody> </tbody>	Q5 <tbody> </tbody>	No <tbody> </tbody>

<tbody>
</tbody>

You may want to use COUNTIF instead to get the result you are after.

 

[trikky]

To get the Pivot you want, the data would need to be in a format like the below.

Thanks.

Won't work for me as the data source can't be changed (SharePoint list). I'll have to go with a less flexible summary.

Appreciate your time and the information you provided.