# Pivot Table: Calculating a product of two columns?

This is a discussion on Pivot Table: Calculating a product of two columns? within the Excel Questions forums, part of the Question Forums category; Can I do something with field settings to calculate the product of two cells in the same row of data? ...

1. ## Pivot Table: Calculating a product of two columns?

Can I do something with field settings to calculate the product of two cells in the same row of data? I.e. I want my quotes column to produce the product of the the same quotes column and the location counts column in the same row of data...Am I thinking correctly that I should/may be able to do that with field settings?

2. ## Re: Pivot Table: Calculating a product of two columns?

It depends on your table. YOu can use calcualted fields to multiply one field by another, but it will work at an aggregate level for any subtotals, in the sense that instead of getting:
Sum(fieldA * fieldB)
you will get:
Sum(fieldA) * sum(FieldB)

For detail rows, it will be correct though.

3. ## Re: Pivot Table: Calculating a product of two columns?

Hi,

Save the data file and (for simplicity) give the data and headers a defined name, not dynamic. Then from a new workbook to create the pivot table and at the first step of the wizard take the external data option. Follow the wizard and at the end take the option to edit in MS Query you can edit the SQL there - hit the SQL button - and add the calculated field there. SQL like below. Exit & complete pivot table. Move the resultant sheet back into the source file if you like. (It is created in a new file to avoid memory leak problems.)

HTH, Fazza

Code:
`SELECT Quote*LocationCount AS [New Name]`

4. ## Re: Pivot Table: Calculating a product of two columns?

Post script. Like this recent thread PivotTable and Access Formulas F

#### Posting Permissions

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