Excel: Use IF Statement in Pivot Table Calculated Field

The following step-by-step example shows how to use an IF statement in a calculated field of a pivot table in Excel.

Step 1: Enter the Data

First, let’s enter the following dataset that contains information about sales transactions made by various employees at some company:

Step 2: Create Helper Column

Our end goal will be to create a pivot table that uses a calculated field to calculate the following value:

(Sum of Revenue – Sum of Cost) * 0.3 IF sales person is Full-Time
(Sum of Revenue – Sum of Cost) * 0.1 IF sales person is Freelancer

In order to use an IF statement in a calculated field, we will first need to create a helper column that assigns a value of 1 to each row with Full-Time as the sales person status or a value of 0 to each row with Freelancer as the sales person status.

We’ll type the following formula into cell E2:

=IF(B2="Full-Time", 1, 0)

We’ll then click and drag this formula down to each remaining cell in column E:

Step 3: Insert the Pivot Table

Next, we’ll use this dataset to create the following pivot table:

Step 4: Add Calculated Field

Next, we will add a calculated field to the pivot table by clicking on any value in the pivot table, then clicking the PivotTable Analyze tab, then clicking Fields, Items & Sets, then Calculated Field:

In the new window that appears, type “Bonus Amount” in the Name field, then type the following in the Formula field:

=IF('Helper'>0, ('Revenue'-'Cost')*.3, ('Revenue'-'Cost')*.1)

Then click Add, then click OK.

Excel pivot table IF statement in calculated field

This calculated field will automatically be added to the pivot table:

We can see that the new column called Sum of Bonus Amount was able to succesfully use an IF statement to calculate the bonus values.

In particular:

The bonus for freelancers was calculated as: (565-145) * .1 = 42
The bonus for full-timers was calculated as: (715-150) * .3 = 169.5

Note: If you’d like to remove the helper column from the final pivot table, simply right click on the Sum of Helper column in the pivot table and then click “Remove Sum of Helper” from the dropdown menu.