Sumifs issue or pivot issue... Help please

[excel_1317]

I have thousands of rows like below. I need to make a pivot for below data based on parent name. Now survey count in pivot shows as 48 which is wrong. If i do average, it gives me 7.4 and using max give me 10. But the correct survey count for ABC Corp. is 14(10+4). How can this be achieved in pivot, may be adding a helper column in the data? My solution is to bring 14 as survey count corresponding to ABC corp. in pivot. Excel masters please help

Parent Name	Survey id	Survey Count
ABC Corp	0123	10​
ABC Corp	0123	10​
ABC Corp
ABC Corp	0123	10​
ABC Corp	0456	4​
ABC Corp	0456	4​
ABC Corp	0456	4​
ABC Corp	0123	10​

 

[jasonb75]

Try using both parent name and survey id in the pivot row box.

 

[excel_1317]

Cannot use survey id in pivot table.

 

[jasonb75]

Like this then?

zgadson 21-7-20.xlsx
	A	B	C	D	E	F	G
1	Parent Name	Survey id	Survey Count	Helper		Row Labels	Sum of Helper
2	ABC Corp	123	10	2.5		ABC Corp	14
3	ABC Corp	123	10	2.5		Grand Total	14
4	ABC Corp
5	ABC Corp	123	10	2.5
6	ABC Corp	456	4	1.3333333
7	ABC Corp	456	4	1.3333333
8	ABC Corp	456	4	1.3333333
9	ABC Corp	123	10	2.5
Sheet9

Cell Formulas
Range	Formula
D2:D9	D2	=IFERROR(C2/COUNTIFS(A:A,A2,B:B,B2),"")

 

[excel_1317]

Thanks @jasonb75. Your solution perfectly did the job. But now I got an additional requirement. Column E has NPS scores for every survey id that corresponds to parent ABC Corp. If I drag NPS to pivot table, it simply takes an average which comes to 73% and that is incorrect. This should be weighted average based on survey id and survey count. Correct weighted average comes to 69% which I calculated in cell I6.

Can this be achieved in pivot table? NPS for ABC Corp should be shown as 69% in pivot table instead of 72%.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Parent Name	Survey id	Survey Count	Helper	NPS
2	ABC Corp	0123	10	2.5	0.628571				Row Labels	Sum of Helper	Average of NPS
3	ABC Corp	0123	10	2.5	0.628571				ABC Corp	14	73%
4	ABC Corp								Grand Total	14	0.726530612
5	ABC Corp	0123	10	2.5	0.628571
6	ABC Corp	0456	4	1.333333	0.857143			Weighted NPS for ABC Corp	69%
7	ABC Corp	0456	4	1.333333	0.857143
8	ABC Corp	0456	4	1.333333	0.857143
9	ABC Corp	0123	10	2.5	0.628571
Sheet3

Cell Formulas
Range	Formula
I6	I6	=((C2*E2)+(C6*E6))/(C2+C6)
D2:D9	D2	=IFERROR(C2/COUNTIFS(A:A,A2,B:B,B2),"")

 

[jasonb75]

Try this formula in K3, must be array confirmed with Ctrl Shift Enter (if not done correctly the result will come out at 11%)

=SUMPRODUCT(IFERROR(($A$2:$A$9=I3)*$D$2:$D$9*$E$2:$E$9,0))/J3

 

[excel_1317]

Sorry, K3 is pivot cell. I2:K4 is pivot table. Like you added an helper column in main data for survey count, can a helper column be added to calculate NPS in pivot table correctly?

 

[jasonb75]

It might be possible, but everything I try gives an incorrect result. Removing the column from the pivot and using formulas instead works fine.

 

[excel_1317]

Yes, the formula you suggested gives accurate result but I have approx 5000 parents with approx 60k rows of data. If i put put formula outside pivot table and end user filters the pivot on parent name then it will be a a problem.

 

[jasonb75]

Try this one in a helper column, then SUM that column in the pivot.

=IFERROR(D2*E2/SUMIF(A:A,A2,D:D),"")