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:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,414
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top