Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11The four most useful Excel PivotTable functions do two different jobs: Sum, Count and Average summarize source records in the Values area; GETPIVOTDATA retrieves a visible result from a PivotTable for use in another worksheet formula. Choose based on what you need the result to mean—and check how blanks, text and filters affect it.
What each function does
Excel calls the first three summary functions: they aggregate a field’s source data inside the PivotTable. GETPIVOTDATA is a separate worksheet function that refers to a result already displayed in a PivotTable. Microsoft documents these capabilities separately; the four-function grouping is a practical way to choose between them.
As an Amazon Associate I earn from qualifying purchases.
| Function | Operation | Useful when you need |
|---|---|---|
| Sum | Adds the field’s values | A total, such as an amount or quantity |
| Count | Counts nonempty values | The number of populated records or entries |
| Average | Calculates the mean of the field’s values | An average that answers the question you are analyzing |
| GETPIVOTDATA | Retrieves a visible PivotTable result by field and item context | A separate worksheet formula that should refer to a PivotTable result |
Choose the right summary function
Sum: add numeric values
Use Sum for additive measures such as sales amounts, expenses or units. Excel uses Sum by default for numeric fields added to the Values area. If the field contains blanks or nonnumeric entries when first added, Excel may choose Count instead. That default reflects the field’s contents, not necessarily a problem with the PivotTable.
If you switch that field to Sum, blanks and nonnumeric values are treated as zero in the PivotTable. If you expected a total and instead see a count, check the field’s summary setting and the values in the source column. Microsoft’s PivotTable guidance explains the default behavior.
Count: count populated entries
PivotTable Count counts nonempty values, including text, and behaves like the worksheet COUNTA function. It is useful when the question is “How many entries are populated?” rather than “How many numeric values are there?” Excel may use Count by default when a value field contains blanks or nonnumeric values.
For a count of numeric cells only, choose Count Numbers instead. Microsoft distinguishes that option from Count: Count Numbers behaves like the worksheet COUNT function. This distinction matters when a field mixes numbers, text and blanks. See Microsoft’s explanation of PivotTable calculations.
Rank #2
- Used Book in Good Condition
Average: calculate a mean
Use Average when the arithmetic mean is the measure you intend to report. A mean can hide variation between records, and it may misrepresent a total-per-unit question when groups have different sizes. For example, averaging group-level averages gives each group equal influence, even if one group contains far more records. In those cases, decide whether you need a mean of individual records or a weighted measure before interpreting the PivotTable.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use GETPIVOTDATA to refer to a PivotTable result
GETPIVOTDATA is useful when a separate worksheet calculation needs a PivotTable result identified by its value field and row, column or filter items, instead of by a fixed cell address. A normal cell reference can point to the wrong result if the PivotTable’s layout changes; GETPIVOTDATA requests the result by its PivotTable context.
Its syntax is GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...). The data_field names the value field, pivot_table refers to a cell or range inside the target PivotTable, and optional field/item pairs specify the intersection to retrieve. To have Excel create a basic formula, type = in another cell, then click the PivotTable value you want to reference.
For example, a formula can request the value for a particular product and region by supplying those field/item pairs. Use the exact field and item names from the PivotTable. Names other than dates and numbers need quotation marks; for date items, use a serial value or a DATE formula so the formula does not depend on how dates are formatted in a particular locale. Microsoft documents the syntax and examples on its GETPIVOTDATA function page.
Check source and visibility limits
- OLAP sources: The standard summary-function choices discussed here are not available in PivotTables based on OLAP source data. Calculation options differ for those PivotTables.
- GETPIVOTDATA visibility: The requested result must be available in the PivotTable. The function can return
#REF!if its PivotTable reference does not point inside a PivotTable or if the requested field/item is not visible—for example, because a filter excludes it. It does not retrieve a hidden result as though it were displayed. - Calculated fields: For non-OLAP PivotTables, calculated-field formulas operate on sums of the underlying data. They cannot use cell references or defined names, or worksheet functions that require cell references or defined names as arguments. Do not assume every worksheet formula can be entered as a calculated-field formula.
Excel provides additional summary choices, including Max, Min, Product, Count Numbers and statistical functions. These four are a focused starting set, not the complete list of PivotTable calculations. For details on calculated fields and other options, see Microsoft’s calculation guide.
Keep the calculation separate from the presentation
PivotTables help summarize and analyze records; PivotCharts can make comparisons, patterns and trends easier to see. Once the measure is correct, a chart may help communicate it—but visualizing a total or average does not change what that measure means. Microsoft’s PivotTable and PivotChart overview describes how the two tools work together.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
