pivot table add field

[olyavv13]

Hello. I have below pivot table based on a database. Would it be possible to add a field in the pivot table based on the if/then condition? for instance, if grand total is >150K for that row, new column will show 1, if >100K but <150K, value shown will be 2, if <100K, value shown will be 3. Pivot table will be changing (rows removed/added) so i would like the values to be updated automatically in the last column of the pvt table, instead of adding this as a separate column outside of the pvt table.


Row Labels Sum Of Amount Calculated field
Apple 250,000 1
Banana 57,000 3
Beans 125,000 2
Orange 5,000 3

 

[Jerry Sullivan]

Re: pvt table add field

Welcome to MrExcel,

Yes, you can add a calculated field to your pivot table using these steps:

1. Select any cell in the Pivot Table.

2. In the Ribbon: Pivot Table Tools Analyze > Calculations group > Fields Items & Sets button > Calculated field....

3. In the Insert Calculated Field window:
a. Enter Name of field to add
b. Enter formula referencing your Amounts field:
=IF(Amount >150000,1,IF(Amount >100000,2,3))
c. Click OK

It wasn't clear how you want to classify the boundaries of exactly 150K and 100K. You can modify the operands to >= as needed.