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**.

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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Sum Two Columns in a Pivot Table in Excel

How to Subtract Two Columns in a Pivot Table in Excel

Excel: Find Percentage Difference Between Two Columns in Pivot Table

Zach, on your final pivot table, the Grand Total for the Sum of the Bonus Amount is incorrect (42 + 169.5 does not equal 295.5). I’m having that issue too. Have you found a way to make the correct grand totals show in the calculated field of the pivot table?