Total of weighted average based on count of unique rows (But in a pivot table)

[Milbourn]

Hi,


I am trying to find the SUM of the WEIGHTED AVERAGE, based on dividing the COUNT of unique combination of values in a row based on three values (COL A, B, and C) DIVIDED by COL D in a pivot table.


Multiple occurrences of data in COL A B and C, causes a duplicate in COL D which I am have a pivot table tracking the grand totals on a separate sheet.


I have a working formula to correct for these duplicates but I have no idea how to transfer this onto a pivot table.


The formula is


SUMPRODUCT(1/COUNTIFS(Data!A2:A36,Data!A2:A36,Data!B2:B36,Data!B2:B36,Data!C2:C36,Data!C2:C36),Data!D2:D36),


which I would have assumed would transfer over to


SUMPRODUCT(1/COUNTIFS(COL A,COL A,COL B,COL B,COL C,COL C),COL D) on the pivot table but it does not.


Can anyone tell me what I am doing wrong?

 

[Milbourn]

Sorry to bump, I've had a bit further look into it and there is apparently a problem with using countifs/sumifs on pivot tables. The way it looks like to get round it is using SQL but I dont know anything about that unfortunately. Is there any workaround available?

 

[Aladin Akyurek]

Would you care to post a small sample of 5 rows from the source data along with the desired result?

 

[Milbourn]

Sure thing, thanks for responding. I’m not sure how to attach files in this forum but I’ve added an example to my drop box. Appreciate any guidance you can give!


https://dl.dropboxusercontent.com/u/79815420/Data.xlsx

Thanks,

Neil

 

[Aladin Akyurek]

Sure thing, thanks for responding. I’m not sure how to attach files in this forum [...]

Try to post a small scal;ed-down sample along with the desired results. Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

 

[Milbourn]

Sorry I can't really download any attachments such as these onto any of my current PC. Is there another upload site that would be more permanent and better accepted?

 

[Milbourn]

Hi again!

I have not come up with a solution to this yet, so was hoping to reopen this thread and ask for help!


So I am trying to have a pivot table for the below information where I can get the sum totals for Identifier A or B, but this total would only count the duplicate rows once. I have the formula for the total (below), but have no idea how to do this on a pivot. Would anyone be able to help?




[TABLE="class: grid, width: 511"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Identifier a[/TD]
[TD]Identifier b[/TD]
[TD]Date.1[/TD]
[TD]Date.2[/TD]
[TD]Values[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Identifier 1[/TD]
[TD]Apples[/TD]
[TD]11/02/2016[/TD]
[TD]24/02/2016[/TD]
[TD]3329[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Identifier 2[/TD]
[TD]Apples[/TD]
[TD]11/02/2016[/TD]
[TD]24/02/2016[/TD]
[TD]1249[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Identifier 2[/TD]
[TD]Apples[/TD]
[TD]11/02/2016[/TD]
[TD]24/02/2016[/TD]
[TD]1249[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Identifier 3[/TD]
[TD]Apples[/TD]
[TD]21/04/2016[/TD]
[TD]04/05/2016[/TD]
[TD]1543[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Identifier 3[/TD]
[TD]Apples[/TD]
[TD]21/04/2016[/TD]
[TD]04/05/2016[/TD]
[TD]1543[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Identifier 3[/TD]
[TD]Apples[/TD]
[TD]21/05/2016[/TD]
[TD]04/06/2016[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Identifier 4[/TD]
[TD]Apples[/TD]
[TD]21/04/2016[/TD]
[TD]04/05/2016[/TD]
[TD]1840[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Identifier 4[/TD]
[TD]Bananas[/TD]
[TD]05/05/2016[/TD]
[TD]18/05/2016[/TD]
[TD]2832[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Identifier 5[/TD]
[TD]Bananas[/TD]
[TD]07/04/2016[/TD]
[TD]20/04/2016[/TD]
[TD]4843[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 323"]
<tbody>[TR]
[TD="colspan: 2"]Sum of Values EXCLUDING DUPLICATES:[/TD]
[TD] 15,788
[/TD]
[/TR]
</tbody>[/TABLE]

=SUMPRODUCT(1/COUNTIFS(A2:A10,A2:A10,C2:C10,C2:C10,D2:D10,D2:D10),E2:E10)

 

[Aladin Akyurek]

What is the desired value: 15,788 or 17,636? What do the red records mean?

 

[Milbourn]

The red records are duplicates I wish to exclude from the grand total.

The total should be 17,636, apologies for the confusion, I changed the data set to make it clearer but forgot to update the total!

 

[Aladin Akyurek]

The red records are duplicates I wish to exclude from the grand total.

The total should be 17,636, apologies for the confusion, I changed the data set to make it clearer but forgot to update the total!

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td] Identifier a[/td][td] Identifier b[/td][td] Date.1[/td][td] Date.2[/td][td] Value[/td][td] Value*UNIQ[/td][/tr]
[tr][td]

2​

[/td][td] Identifier 1[/td][td] Apples[/td][td] 2/11/2016[/td][td] 2/24/2016[/td][td] 3329[/td][td] 3329[/td][/tr]
[tr][td]

3​

[/td][td] Identifier 2[/td][td] Apples[/td][td] 2/11/2016[/td][td] 2/24/2016[/td][td] 1249[/td][td] 624.5[/td][/tr]
[tr][td]

4​

[/td][td] Identifier 2[/td][td] Apples[/td][td] 2/11/2016[/td][td] 2/24/2016[/td][td] 1249[/td][td] 624.5[/td][/tr]
[tr][td]

5​

[/td][td] Identifier 3[/td][td] Apples[/td][td] 4/21/2016[/td][td] 5/4/2016[/td][td] 1543[/td][td] 771.5[/td][/tr]
[tr][td]

6​

[/td][td] Identifier 3[/td][td] Apples[/td][td] 4/21/2016[/td][td] 5/4/2016[/td][td] 1543[/td][td] 771.5[/td][/tr]
[tr][td]

7​

[/td][td] Identifier 3[/td][td] Apples[/td][td] 5/21/2016[/td][td] 6/4/2016[/td][td] 2000[/td][td] 2000[/td][/tr]
[tr][td]

8​

[/td][td] Identifier 4[/td][td] Apples[/td][td] 4/21/2016[/td][td] 5/4/2016[/td][td] 1840[/td][td] 1840[/td][/tr]
[tr][td]

9​

[/td][td] Identifier 4[/td][td] Bananas[/td][td] 5/5/2016[/td][td] 5/18/2016[/td][td] 2832[/td][td] 2832[/td][/tr]
[tr][td]

10​

[/td][td] Identifier 5[/td][td] Bananas[/td][td] 4/7/2016[/td][td] 4/20/2016[/td][td] 4843[/td][td] 4843[/td][/tr]
[/table]

In F2 control+shift+enter, not just enter, and copy down:

=1/COUNT(IF(A2=$A$2:$A$10,IF(B2=$B$2:$B$10,IF(C2=$C$2:$C$10,IF(D2=$D$2:$D$10,1)))))*E2

Then run a pivot table on this extended source:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#DDEBF7"]Row Labels[/td][td="bgcolor:#DDEBF7"]Sum of Value*UNIQ[/td][/tr]
[tr][td]

2​

[/td][td]Identifier 1[/td][td]

3329​

[/td][/tr]
[tr][td]

3​

[/td][td]Identifier 2[/td][td]

1249​

[/td][/tr]
[tr][td]

4​

[/td][td]Identifier 3[/td][td]

3543​

[/td][/tr]
[tr][td]

5​

[/td][td]Identifier 4[/td][td]

4672​

[/td][/tr]
[tr][td]

6​

[/td][td]Identifier 5[/td][td]

4843​

[/td][/tr]
[tr][td]

7​

[/td][td="bgcolor:#DDEBF7"]Grand Total[/td][td="bgcolor:#DDEBF7"]

17636​

[/td][/tr]
[/table]