Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to write formulas for a pivot table calculated field in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Q1")/SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Q1")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Quarter | Revenue | Units |
| East | Q1 | 5000 | 20 |
| East | Q1 | 3000 | 15 |
| West | Q1 | 4000 | 10 |
| East | Q2 | 6000 | 25 |
| West | Q2 | 2000 | 8 |
=SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Q1")/SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Q1")→228.5714285714
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1List your flat data in columns: A = Region, B = Quarter, C = Revenue, D = Units (rows 2-7).
- 2Decide the calculated metric — for example, Revenue per Unit = Revenue / Units.
- 3In a summary cell, write =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Q1") to get total revenue for East/Q1.
- 4Divide by a matching SUMIFS on D2:D7 to get the aggregated Revenue per Unit ratio.
- 5Replace the hardcoded strings with cell references in a summary table to replicate pivot-like row/column headers.
Tips
- A pivot calculated field divides summed values, not row-by-row values — your SUMIFS formula should reflect the same aggregation order to match pivot output.
- Wrap the entire expression in IFERROR(...,0) to handle quarters with no data gracefully.
- For a percentage-of-total calculated field, use SUMIFS for the numerator and a broader SUMIFS (without the item filter) for the denominator.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why doesn't my calculated field give the same result as dividing two columns manually?
Pivot calculated fields operate on aggregate sums, not on each row. Revenue/Units in a calculated field computes SUM(Revenue)/SUM(Units), which differs from SUM(Revenue/Units).
Can I reference an external cell in a pivot calculated field?
No. Calculated fields can only reference other pivot fields by name. If you need an external value, use a SUMIFS formula outside the pivot instead.
Does Google Sheets support calculated fields in pivot tables?
Yes, but with fewer formula functions than Excel. The SUMIFS approach works in both without any restrictions.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.
How-toHow to group data by month using formulas instead of a pivot
Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.