# Thread: Sumproduct formula in pivot table

1. ## Sumproduct formula in pivot table

Masters,

Good day!

Is it possible to use sumproduct formula in pivot table?

I've attached a template of my data showing computation in pivot using average and a sumproduct result using same problem. Just see sheets name (L4,L5 are sumproduct and REPORT sheet is pivot)

Regards,

Math

2. No it isn't. A calculated field wouldn't work for this because it will sum the columns before performing any other operation, so if you create a column using:

it will calculate as:

rather than

which is what you want. Do you have access to Power Pivot?

3. ## The Following User Says Thank You to rory For This Useful Post:

Mathdarkmoon (2015-03-17)

4. Hi Math

As Rory says, you cannot have a sumproduct formula directly, but I believe you can achieve the same result using calculated fields together with some addition data columns in your table.
I can get the sumproduct results you want in the pivot table using this method.
See attached file.

Your [Database] sheet has an error or glitch in cell [G2024].
(This is why there is a #VALUE in my REPORT)

NOTE: Although we can't post .xlsb files on this forum, I would recommend you use this Excel binary file format for you files.
They will be considerable smaller and therefore faster to load, particularly on a network (and thus reducing network traffic).
Try it.

zeddy

5. ## The Following User Says Thank You to zeddy For This Useful Post:

Mathdarkmoon (2015-03-17)

6. @rory

I want the option two if it is possible? Yes I have access with power pivot but don't know how to use it..

@zeddy

Thanks, I also found the error from the data... So this is the easiest way to have weighted average in pivot, It will help me a lot...

Thanks to both of you..

7. If you don't want to use Power Pivot, zeddy's solution is the simplest.

8. ## The Following User Says Thank You to rory For This Useful Post:

Mathdarkmoon (2015-03-17)

9. @rory

If it will make the database easy and no more many formulas around, why not?

Can you share how to do it in power pivot? I could easily follow instructions :-D

10. OK. First you need to convert the Database range into a Table.
Then I suggest you clean up your data columns so that they contain numbers only not text like "-".
Then on the Power Pivot tab, click the Create Linked Table button, which will load your table in to Power Pivot.
You can now add a calculated column in PP using a formula like:
(I then named the column "Weighted FE") and similarly for each column you want a weighted average for.
Next, click the Pivot Table button on the Home tab of Power Pivot and choose where to put it (to select a range you need to click the grid button on the right of the dialog, otherwise you have to type the address in).
Then you can add a measure by right clicking the table name in the Field List and use the formula:
which will return the weighted average you want.

It's effectively the same as what zeddy showed but all the calculations are done in PP rather than in the table on the worksheet.

11. @rory

Thanks for the info I'll have already made database based on your instructions but still I have questions..

If I update my database does the database(pivot) will update automatically or I need to click refresh everytime database is updated

Also I cannot add measure in the pivot, Tried your instruction but still got the wrong answer..

Thanks and regards,

Math

PS: Is it allowed to post other uploader site link? I've used parse just incase it is now allowed.

TemplateV2

12. I can't open that file - what version of Excel / Power Pivot are you using?

13. I am using 64bit of MS and Power pivot before the latest released from MS (i think 2008R2 for pivot..)

14. 2010 or 2013?

15. Sorry I forgot to input it's 2010 sir...

16. I don't think I have 64bit 2010 installed anywhere. If I find the time I'll set up a VM to see what you have in that file.

17. I already have the solution... Thank you!

By the way, I have one question. If I update/input in the database, does the power pivot automatically update?

Regards,

Math

18. You have to refresh the pivot table but that's all.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•