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

T

trikky

New Member
Joined
Dec 28, 2016
Messages
32
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.

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]
A​
[/TD]
[TD="width: 64, bgcolor: transparent"]
B​
[/TD]
[TD="width: 64, bgcolor: transparent"]
C​
[/TD]
[TD="width: 64, bgcolor: transparent"]
D​
[/TD]
[TD="width: 64, bgcolor: transparent"]
E​
[/TD]
[TD="width: 64, bgcolor: transparent"]
F​
[/TD]
[TD="width: 64, bgcolor: transparent"]
G
​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Name​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Month​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q2​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q5​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
2​
[/TD]
[TD="width: 64, bgcolor: transparent"]Fred
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
3​
[/TD]
[TD="width: 64, bgcolor: transparent"]Betty
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]No

[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]Barney
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]No

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[TD="width: 64, bgcolor: transparent"]Wilma
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
6​
[/TD]
[TD="width: 64, bgcolor: transparent"]Fred
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No

[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
7​
[/TD]
[TD="width: 64, bgcolor: transparent"]Betty
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
8​
[/TD]
[TD="width: 64, bgcolor: transparent"]Barney
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
9​
[/TD]
[TD="width: 64, bgcolor: transparent"]Wilma
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
10​
[/TD]
[TD="width: 64, bgcolor: transparent"]Fred
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
11​
[/TD]
[TD="width: 64, bgcolor: transparent"]Betty
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
12​
[/TD]
[TD="width: 64, bgcolor: transparent"]Barney
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]
Q1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q2​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Q5​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Yes
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]No
[/TD]
[TD="width: 64, bgcolor: transparent"]
6​
[/TD]
[TD="width: 64, bgcolor: transparent"]
3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]N/A
[/TD]
[TD="width: 64, bgcolor: transparent"]
1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[TD="width: 64, bgcolor: transparent"]
3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[/TR]
</tbody>[/TABLE]

or

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]
Yes
​
[/TD]
[TD="width: 64, bgcolor: transparent"]
No​
[/TD]
[TD="width: 64, bgcolor: transparent"]
N/A​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
Q1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
6​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
Q2​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
Q3​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
Q4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
4​
[/TD]
[TD="width: 64, bgcolor: transparent"]
3​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
Q5​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[TD="width: 64, bgcolor: transparent"]
5​
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
To get the Pivot you want, the data would need to be in a format like the below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Month[/TD]
[TD]Q[/TD]
[TD]y/n/na[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Feb[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Feb[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Feb[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Feb[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Feb[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Betty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Betty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Betty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Betty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Betty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Jan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Q5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


You may want to use COUNTIF instead to get the result you are after.
 
Upvote 0
mrshl9898 said:
To get the Pivot you want, the data would need to be in a format like the below.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
222
Tetra201
Tetra201
O
  • Solved
Helpful formatting/delimeters for a multi-use text box?
Replies
4
Views
238
Office_Drone
O
V
  • Question Question
Formula Help
Replies
1
Views
212
arun_eagertolearn
A
R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
774
Rnkhch
R
J
  • Question Question
Financial Impact by Quarter weighted by the month?
Replies
0
Views
468
johncon60
J

Forum statistics

Threads
1,225,741
Messages
6,186,763
Members
453,370
Latest member
juliewar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top