Sumproduct formula inside Pivot Table

[f14]

Hi,

I was wondering if there's a way to place sumproduct formula inside pivot table?

I have a data that looks like this:

<table width="248" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 53pt;" width="70"> <col style="width: 43pt;" span="2" width="57"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 48pt;" height="17" width="64">Date</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="70">Name</td> <td class="xl68" style="width: 43pt;" width="57">Count</td> <td class="xl69" style="border-left: medium none; width: 43pt;" width="57">Average Time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">1-Dec</td> <td class="xl71" style="border-top: medium none;">A</td> <td class="xl72" style="border-top: medium none;">135</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">526</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">1-Dec</td> <td class="xl74" style="border-top: medium none;">B</td> <td class="xl72" style="border-top: medium none;">193</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">632</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">1-Dec</td> <td class="xl71" style="border-top: medium none;">C</td> <td class="xl72" style="border-top: medium none;">183</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">659</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">1-Dec</td> <td class="xl71" style="border-top: medium none;">D</td> <td class="xl72" style="border-top: medium none;">221</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">540</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">1-Dec</td> <td class="xl71" style="border-top: medium none;">E</td> <td class="xl72" style="border-top: medium none;">185</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">601</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">2-Dec</td> <td class="xl71" style="border-top: medium none;">A</td> <td class="xl72" style="border-top: medium none;">178</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">644</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">2-Dec</td> <td class="xl71" style="border-top: medium none;">C</td> <td class="xl72" style="border-top: medium none;">135</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">526</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">3-Dec</td> <td class="xl71" style="border-top: medium none;">D</td> <td class="xl72" style="border-top: medium none;">193</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">632</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">3-Dec</td> <td class="xl74" style="border-top: medium none;">B</td> <td class="xl72" style="border-top: medium none;">185</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">601</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt;" height="17">3-Dec</td> <td class="xl71" style="border-top: medium none;">E</td> <td class="xl72" style="border-top: medium none;">135</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">526</td> </tr> </tbody></table>

I would like to place this in a pivot table so I could use the ability to filter dates but I also would like to get the average time using the data I have.

I would appreciate any help.

 

[Juan Pablo González]

You mean you need to calculate the average by multiplying "AverageTime" * "Count" and then divide that by the Sum of "Count" ?

If so, then I think it's best to add a new column to your data with the multiplication.

 

[f14]

Thanks, Juan!

That's exactly what I need!

 

[Juan Pablo González]

So... did you manage to do it? confused by your answer.

If your data is in A1:D11, then put in

E1

Multiply

and in E2:E11

=C2*D2

then, in the Pivot Table, create a Calculated Field called "Compound Average" with the formula

=Multiply / Count

and add it to the Pivot Table.

 

[f14]

Yes, that's what I did!

Thanks again!

 

[Neologism]

Adding an extra line into the Pivot Table Source data is not always the best option. Isn't there a way to put that sum product line into a pivot tables calculated field?

My thought process is such:

=SUMPRODUCT('Count', 'Average Time')/SUM('Count')

This feels fairly intuitive but alas it does not give the desired results. Would perhaps someone be the wiser?