Google Sheets Add Calculated Field for Pivot Table with COUNTIF

In Google Sheets, the Pivot Table can be a very useful tool to summarize a huge dataset. To analyze data, you can insert any general function from the Pivot table editor. Moreover, you can also customize any formula in the Calculated Field option. In this article, we will explain the pivot table calculated field for COUNTIF in Google Sheets.

overview of google sheets pivot table calculated field countif

A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.

Steps to Add Calculated Field with COUNTIF Formula for Pivot Table in Google Sheets

As the COUNTIF function is not placed in the pivot table formulas, so we have to customize the COUNTIF formula. To do so, before going to the main steps we have to create a dataset that represents sales of the different regions for different months. Here we develop columns namely: Region, April Sales, May Sales, and June Sales.

dataset of google sheets pivot table calculated field countif

Step 1: Creating Pivot Table

After developing the dataset to create a pivot table,

  • First, go to the Insert bar and select the Pivot table option.

create pivot table in google sheets

  • After clicking on the Pivot table option, you will find the Create pivot table box.
  • Now input the Data range and Insert to. Here we add the entire dataset as the Data range and for Insert to option select the New sheet option.
  • Then press on Create.

insert range and set different sheet for the pivot table

  • Immediately, you will find a new sheet name Pivot table is created.

pivot table in google sheets

  • Now you can add any rows, columns, or values in the table. Here, we first add the Region as Rows.

add rows in the pivot table

  • After adding the Region from the list, you will find it in the main table.

insert region name in the pivot table

Step 2: Setting Up Values in Pivot Table

This is just an example of how we can add column values in a pivot table. This step is optional.

You can insert any formulas in the table by using the Values bar. For this,

  • First, go to the Values option select Add and click on any title that you want to insert. Here, we select the April Sales option.

add sale value to sum up

  • Then for the Summarized by option select the SUM formula. And you will find the Sum of April Sales automatically add to the Region column.

input sun formula in the pivot table

  • You can also apply all these formulas in the table and get your desired result.

formulas in pivot table

Similar Readings

Step 3: Inserting Calculated Field with COUNTIF Formula

Now for the meat of our article. As we can see in the last image, in the formula list the COUNTIF formula is not available. But you can also resolve this problem and easily apply the COUNTIF formula in Google Sheets by using the Calculated Field. To do so,

  • First, go to the Values and click on Add. then select the Calculated Field option.

insert calculated field option to insert countif formula

  • After clicking on the Calculated Field, a column automatically adds to the table, and you can find a Formula bar where you can apply the customized formula.

Insert customize formula in formula bar

  • Now insert the COUNTIF formula in the Formula bar.
=COUNTIF(‘April Sales’,”>$500”)
  • Here, we are trying to count the number of Sales greater than $500 for April.
  • We do not get the correct results yet, so we must make one more change.

insert countif formula in google sheeets

  • Then select the Custom option from the Summarized by drop-down.

set custom in pivot table

Read More: [Fixed!] COUNTIF Function Is Not Working in Google Sheets

Step 4: Finalizing Outcome

  • Finally, you can find the output of the COUNTIF formula in the Calculated Field 1 column.
  • With Custom selected, the pivot table will automatically update the results in the column.

outcome of google sheets pivot table calculated field countif

  • You can further customize the column by changing the heading.
  • This can be done by double-clicking on the column heading to edit the text.
  • Here we change the two-value column title and insert “Total Sales in April” and “Count of Sales over $500 in April”.

outcome of google sheets pivot table calculated field countif

  • Additionally, by applying the same process we add the count of sales over $500 for the month of May.
=COUNTIF('May Sales',">$500")

final output of google sheets pivot table calculated field countif

Read More: How to Use VLOOKUP with COUNTIF Function in Google Sheets

Things to Remember

  • Careful about inserting values and formulas.
  • In the custom formula use a single quotation for the column heading.


We believe this article will help you to get a clear concept of the Google Sheets pivot table calculated field by using the COUNTIF formula. To explore more about pivot tables in google sheets you can visit the OfficeWheel website.

Related Articles

Annyca Tabassum

Annyca Tabassum

Hello, I'm Annyca Tabassum. I currently work for the SOFTEKO as a technical writer and content developer. I have completed my graduation and post-graduation from SUST.

We will be happy to hear your thoughts

Leave a reply