You generally cannot enter SUMPRODUCT directly as a PivotTable value calculation: a standard PivotTable summarizes source fields, while SUMPRODUCT works on worksheet arrays or ranges. For a weighted calculation, add a suitable helper column to the source data, use a Data Model measure, or calculate the result beside the PivotTable; for example, a weighted average outside the PivotTable can use =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5).
What SUMPRODUCT and a PivotTable Each Do
SUMPRODUCT multiplies corresponding items in one or more arrays and then adds those products. It is useful when each row has a quantity and a rate, score and weight, or other pair of values whose products must be totaled. A PivotTable, by contrast, groups records by categories and summarizes numeric fields, commonly by Sum, Count, or Average. The two features can support the same analysis, but they work at different levels.
For instance, a sales table might contain Region, Product, Quantity, and Unit Price. SUMPRODUCT can calculate the total of Quantity multiplied by Unit Price across rows. A PivotTable can group those rows by Region or Product and display sums. If the source also contains a Line Total field, the PivotTable can sum that field by any chosen category.
The key distinction is that SUMPRODUCT normally receives worksheet ranges or arrays. PivotTable value fields summarize fields in the source; they are not ordinary worksheet formulas that can be replaced with a range-based function. That is why the best method depends on whether you need one overall calculation, a grouped summary, or a reusable calculation within the PivotTable.
Why SUMPRODUCT Is Not a Direct PivotTable Value
In a standard PivotTable, the Values area accepts source fields and summarizes them. Dragging Quantity into Values and choosing Sum totals quantities; dragging Unit Price in and choosing Average produces an unweighted average of the listed prices. Neither action calculates each row’s Quantity multiplied by Unit Price and then adds the products.
A regular PivotTable can also have a calculated field. On Windows, select the PivotTable and choose PivotTable Analyze > Fields, Items & Sets > Calculated Field. A calculated field can use source field names to calculate a row-level expression such as quantity multiplied by unit price, after which the PivotTable summarizes the result. It does not work like a worksheet cell formula with direct references such as B2:B5, which is what SUMPRODUCT needs.
There is an important difference for PivotTables built from the Data Model or an external OLAP source: the regular Calculated Field command may not be available. Those models use measures, typically written in DAX, for calculations over data. If your goal is simply to total quantity times price by category, a source helper column is usually the most straightforward option.
Calculate a Weighted Average with SUMPRODUCT
A common reason to search for “PivotTable SUMPRODUCT” is to calculate a weighted average. An ordinary average gives every row equal influence. A weighted average gives each value influence according to its associated weight. For unit prices, quantity is often the weight: selling 100 units at one price should affect the average more than selling one unit at another price.
Suppose rows 2 through 5 contain quantities of 2, 1, 3, and 2 in column B, and unit prices of $10, $12, $8, and $9 in column C. With quantities in B2:B5 and prices in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5). SUMPRODUCT calculates 2×10 + 1×12 + 3×8 + 2×9, or 74; total quantity is 8, so the weighted average price is 74/8, or $9.25.
That formula returns one result for the whole selected range. To get a separate weighted average for each region, you need to apply the region condition to both the weighted total and the total weight. If region names are in A2:A5, quantities in B2:B5, and prices in C2:C5, with the region to evaluate entered in E2, use the following formula in a worksheet cell:
With regions in A2:A5, quantities in B2:B5, prices in C2:C5, and the requested region in E2, use =SUMPRODUCT((A2:A5=E2)*B2:B5*C2:C5)/SUMIFS(B2:B5,A2:A5,E2). For the example rows, if all four records are in the region named in E2, the result is $9.25. If no matching rows have a positive total quantity, the denominator is zero and Excel returns a division error; handle that case if it is possible in your data.
Keep the ranges the same size. SUMPRODUCT expects corresponding array dimensions to align; mixing, for example, a four-row condition range with a five-row quantity range can produce an error or an unintended result. Also make sure the values that should be multiplied are numeric rather than numbers stored as text.
Use a Helper Column for a PivotTable Summary
If you want PivotTable totals grouped by product, month, or region, add a Line Total field to the source data. With Quantity in column B and Unit Price in column C, and with the first data row at row 2, enter this in D2: =B2*C2. The result for the first example row is $20. Fill the formula down the source records, then create or refresh the PivotTable and drag Line Total into Values. Choose Sum if Excel has not selected it automatically.
A structured Excel Table makes this easier to maintain as records are added. Select the source range and choose Insert > Table (or press Ctrl+T on Windows); confirm that the table has headers. In its new Line Total column, enter a row formula such as =[@Quantity]*[@[Unit Price]]. Excel fills the formula down the table column, and new table rows generally inherit the calculated-column formula. Mac users can create a table through Insert > Table; keyboard shortcuts can differ by keyboard and Excel release.
To build the summary, select a cell in the source table and choose Insert > PivotTable. Choose where to place it, then arrange fields: Region in Rows, Product in Columns if useful, and Line Total in Values. The result is an ordinary PivotTable of row-level calculated totals, and you can change the grouping without rewriting the multiplication formula. When source records change, right-click the PivotTable and choose Refresh.
This method is easy to inspect because the calculation is visible on each source record. It also works when the same line total is useful outside the PivotTable. Its trade-off is that it adds a field to the source, and the PivotTable must be refreshed to reflect changed or added records.
Use a Calculated Field for Simple Row-Level Math
For a standard PivotTable, a calculated field can avoid editing the source layout when the calculation is a straightforward expression based on source fields. Select the PivotTable, then use PivotTable Analyze > Fields, Items & Sets > Calculated Field. Give the field a name such as Line Total and construct an expression using the available field names. In a basic sales source, the calculation is the equivalent of Quantity multiplied by Unit Price; add the resulting field to Values and summarize it by Sum.
Calculated fields are not the same as formulas entered beside a PivotTable. They calculate using the source fields and are summarized by the PivotTable. They can also behave differently from a helper column in certain layouts, so check the results against a few source records, especially when the formula uses division or when subtotal and grand-total interpretations matter. Use a helper column when you need a transparent row-by-row result or want to reuse it elsewhere.
Do not expect a calculated field to accept a worksheet formula that points at a range such as B2:B5. That is the central limitation behind the question: SUMPRODUCT’s range-based operation belongs in a worksheet formula, a source-data calculation, or an appropriate model measure rather than as a conventional PivotTable aggregation.
Choose the Right Method for the Analysis
- Use SUMPRODUCT in a worksheet when you need one weighted total or weighted average from selected ranges, or need a formula-driven result beside a report.
- Use a helper column when each source row has a meaningful calculated value, such as quantity times price, and you want the PivotTable to group and sum those values.
- Use a calculated field for simple formulas built from source fields in a conventional PivotTable, provided the calculation behaves as required.
- Use a Data Model measure when the PivotTable is based on the Data Model and the calculation must respond to filters and groupings without adding a physical source column. Measures require learning the model’s calculation language, DAX.
For example, if a report only needs total revenue by month, a Line Total helper column followed by Sum is clear and robust. If it needs a weighted average price by region, an unweighted Average of Unit Price is not equivalent; use a weight-aware calculation. If the summary must recalculate within a large model with several related tables, a DAX measure may be more suitable than a worksheet formula. A formula placed beside a PivotTable is quick, but it can become misaligned if the PivotTable expands or its layout changes.
Common Errors and Checks
When a SUMPRODUCT result looks wrong, first check the numerator and denominator separately. In the weighted-average example, the numerator should be 74 and the total quantity should be 8. If the formula includes a condition, verify that the condition matches the text exactly apart from ordinary wildcard behavior, and confirm that the matching rows contain numeric weights.
Other useful checks include:
- Confirm every range starts and ends on the same rows and has the same number of cells.
- Check for blank, text, or error values in fields expected to contain numbers.
- Make sure the PivotTable’s Value Field Settings are set to Sum, not Count or Average, when summing a helper field.
- Refresh the PivotTable after editing the source. If new rows fall outside a normal fixed source range, use an Excel Table as the source or update the PivotTable data source.
- Compare one group total with a manual calculation from a few source rows before relying on the complete report.
One frequent misconception is that averaging prices in a PivotTable produces an average price per item. It produces an average of the row prices, where each record has equal influence. A weighted average accounts for quantities, so the two results can differ substantially when transaction sizes vary.
Frequently Asked Questions
Can I put SUMPRODUCT in a PivotTable Values area?
No, not as a normal range-based worksheet formula. Use a helper column or calculated field for row-level math, a Data Model measure for a model-based calculation, or a SUMPRODUCT formula in a worksheet cell outside the PivotTable.
Can I calculate a weighted average in a PivotTable?
Yes, but an ordinary Average of the value field is not weighted. Create an appropriate row-level calculation and summarize its numerator and denominator, or use a Data Model measure designed to calculate the weighted result for each filtered group.
Why does my PivotTable show Count instead of Sum?
Excel may choose Count if it detects text or blanks in the source field, or if the field is not consistently numeric. In the PivotTable, open Value Field Settings and select Sum when available; also inspect and correct the source values.
Does SUMPRODUCT work with criteria?
Yes. A logical test such as a range equaling a region name creates an array of matches, which can be multiplied by numeric arrays inside SUMPRODUCT. Keep the ranges aligned and make sure the denominator in a weighted average is not zero.
Do I need to refresh after adding a helper-column formula?
Yes. Refresh the PivotTable after source values or formulas change. If you added new records beyond a fixed source range, expand or change the source range; using an Excel Table helps the source grow with new rows.
Bottom Line
SUMPRODUCT is a worksheet function, not a direct PivotTable value summarization option. For grouped totals, calculate the row-level product in a helper column or use a suitable calculated field; for weighted averages, divide the weighted sum by the sum of the weights. Choose the approach that keeps the calculation understandable and gives the right result when the PivotTable is filtered, regrouped, and refreshed.
Related
- Excel NOW Format: Show the Current Date and Time Correctly
- Excel Not Responding When Copying and Pasting? Fixes
- Excel Not Updating Links From Other Workbooks? Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Tables: Advanced Features and AnalysisYes. Excel PivotTables support advanced analysis through features such as calculated summaries, percentage and running-total views, date grouping,…
- Google Sheets Chart of Accounts Template and SetupA Google Sheets chart of accounts template is a structured list of the accounts a business uses to…
- Excel Chart Logarithmic Scale: Setup and TroubleshootingTo add a logarithmic scale to an Excel chart in Microsoft 365 for Windows, select the chart’s numeric…
- Custom Trendline in Excel: How to Add and Format OneTo add a custom trendline in Excel for Microsoft 365 on Windows, select a supported chart, then choose…
- Pivot Chart Filters in Excel: How to Add and Use ThemYes. In Excel for Microsoft 365 on Windows, select the PivotChart and use its field-button drop-downs to filter,…
- Pivot Table Excel: How to Create and Use OneTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.