PivotTable Fields Setting

[ChGatsby]

Hi, I'm trying to create a PivotTable, but I can't seem to find the right settings for the PivotTable fields to achieve my goal. See the [Goal] sheet in the attached sample file. Thank you!

[dflak]

If you really want to use a pivot table, then you are going to have to normalize the data.

You are better off using COUNTIF formulas like on the Goal sheet.

[alansidman]

an alternative solution is with power query which allows you to unpivot your source data to a normalized presentation and then pivot it.

Please Login or Register  to view this content.

Excel 2016 (Windows) 64 bit

	A	B	C	D
8	Attribute	Yes	No	N/A
9	Q1	14	4
10	Q10	14	4
11	Q11	18
12	Q12			18
13	Q13			18
14	Q14			18
15	Q15	16	2
16	Q16			18
17	Q17			18
18	Q18			18
19	Q19			18
20	Q2	17	1
21	Q20			18
22	Q21			18
23	Q22			18
24	Q23			18
25	Q24			18
26	Q25			18
27	Q3	14	4
28	Q4	16	2
29	Q5	18
30	Q6	18
31	Q7	10	8
32	Q8	13	5
33	Q9	14	4

Sheet: Pivot

[josephteh]

Alternative formula, B2=SUMPRODUCT((Table1[[Q1]:[Q25]]=B$1)*(Table1[[#Headers],[Q1]:[Q25]]=$A2)), copy across and down.