Google Sheets Updated November 2026

Calculated field in Google Sheets

In the pivot table editor click Add next to Values, choose Calculated field, and type a formula using column names, such as =Revenue - Cost. With Summarize by set to SUM the formula runs on each source row and the results are summed; with Custom you write aggregate formulas like =SUM(Revenue)/SUM(Units).

Syntax

Pivot table editor > Values > Add > Calculated field > Formula: =Revenue - Cost > Summarize by: SUM | Custom

Part	What it is
`Formula`	Column headers as names; wrap headers with spaces in single quotes: ='Unit Price' * Units
`Summarize by SUM`	Row by row evaluation, then summed per pivot row
`Summarize by Custom`	You supply the aggregation: =SUM(Revenue)/COUNTA('Order ID')

Examples

Calculated field: =Revenue * 0.05, Summarize by SUM
Rows: Rep

Result: A Commission column equal to 5% of each rep's revenue.

Calculated field: =SUM(Revenue)/SUM(Units), Summarize by Custom
Rows: Product

Result: Average price per unit for each product, weighted correctly.

Calculated field: =COUNTUNIQUE(Customer), Summarize by Custom
Rows: Channel

Result: Unique customers per channel.

Common mistakes

The field shows #ERROR: a column name is misspelled or has a space without quotes.
=Revenue/Units with SUM gives nonsense: it sums per-row ratios; switch to Custom and use =SUM(Revenue)/SUM(Units).
The calculated field does not appear in Rows: calculated fields can only be Values in Sheets.

Practice Calculated field in Google Sheets

These drills check your answer on live data, free, no account needed.

Commission checkYour manager pays commission on revenue per rep. List every Rep with total Revenue, highest first.
Units and revenue togetherShow both total Units and total Revenue for each Product.

Questions

How is this different from an Excel calculated field?

Excel always applies the formula to summed fields. Sheets lets you choose: SUM applies the formula per row first, Custom lets you aggregate explicitly, which is more flexible.

Can I use IF in a Google Sheets calculated field?

Yes with Summarize by SUM, for example =IF(Status="Approved", Amount, 0), because the formula is evaluated row by row.

Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.
Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.
Distinct countCount unique customers or products per region. In Excel tick Add this data to the Data Model and pick Distinct Count; in Google Sheets choose COUNTUNIQUE.