pivot table formula

G

Guest

Guest
I'm trying to use a custom pivot table formula with an "if" command. Can I set up a pivot table field that basically says, if field1="yes", then field2*field3, else 0 and thereby get the sumproduct of fields 2 and 3 wherever field 1 is equal to "yes". This would seem to be more logical than creating intermediate fields in my database.
Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
On 2002-03-11 16:39, Anonymous wrote:
I'm trying to use a custom pivot table formula with an "if" command. Can I set up a pivot table field that basically says, if field1="yes", then field2*field3, else 0 and thereby get the sumproduct of fields 2 and 3 wherever field 1 is equal to "yes". This would seem to be more logical than creating intermediate fields in my database.
Thanks

It may seem more logical, but it wouldn't (if it could be done -- and it can't) achieve your desired end. It would fowl up the order of operations since Calculated Fields sum the fields before performing the arithmetic operations. See the Help topic for "Syntax for calculated field and item formulas in PivotTables" where it states "Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula".

You'd get the product of the sums instead of the sum of the products!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top