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

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.


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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
To get the Pivot you want, the data would need to be in a format like the below.

NameMonthQy/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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,596
Messages
5,832,640
Members
430,150
Latest member
amitk1

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
Top