SUMPRODUCT and summary grids
Updated · Works in: Excel 2021+, Microsoft 365, Google Sheets · Every example checked before release
Short answer
=SUMPRODUCT(F2:F13, G2:G13) multiplies pairs and adds them. Use it when a condition needs a calculation SUMIFS can't do, like =SUMPRODUCT((MONTH(A2:A17)=3)*E2:E17), or for weighted sums. For a summary grid, one SUMIFS with $H2 and I$1 fills every cell.
SUMPRODUCT multiplies ranges row by row and adds up the products. It's the oldest way to do array maths in a spreadsheet, and it still works everywhere.
Quantity × price, summed
SUMPRODUCT(F2:F13, G2:G13) is the sum of Qty × Price for every order: total revenue without a helper column.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Revenue | $397.25 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
Conditional totals
Conditions become 1s and 0s when multiplied, so SUMPRODUCT((B="North")*(C="Fern")*E) adds only the matching rows, like SUMIFS.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | North Fern | $1,738.50 | |
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | |||
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | |||
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | |||
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | |||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | |||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | |||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | |||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | |||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | |||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | |||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | |||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | |||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | |||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | |||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | |||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
Summary grids with SUMIFS
For a cross-tab, one SUMIFS with mixed references ($H2 and I$1) fills every cell when you copy it across and down.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | Revenue | Fern | Monstera | ||
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | North | $1,738.50 | $2,006.00 | ||
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | South | $1,387.00 | $1,666.00 | ||
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | |||||
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | |||||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | |||||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | |||||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | |||||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | |||||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | |||||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | |||||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | |||||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | |||||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | |||||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | |||||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | |||||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
Top N
LARGE(range, k) is the k-th biggest value and SMALL(range, k) the k-th smallest. Look up who it belongs to with INDEX/MATCH or XLOOKUP. Give k as a list in braces to get several at once: SUM(LARGE(E2:E9, {1,2,3})) adds the top three.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Rank | Rep |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | 1 | Lina Chen |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | 2 | Hugo Martin |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | 3 | Farid Haddad |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | ||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | ||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | ||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | ||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Check yourself
One real exercise from the “Reporting patterns” unit. Checked on our server; no account needed.Fill the same grid with units instead of revenue. One formula in I2, copied across and down.
- Use SUMIFS
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | Units | Fern | Monstera | ||
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | North | ||||
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | South | ||||
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | |||||
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | |||||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | |||||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | |||||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | |||||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | |||||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | |||||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | |||||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | |||||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | |||||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | |||||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | |||||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | |||||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
Common mistakes
Ranges of different sizes (#VALUE!).
Text in a range: with comma-separated arrays it counts as 0, but inside a
*expression it gives #VALUE!.Using SUMPRODUCT on whole columns (A:A) in big files: it's slow.
Questions
SUMPRODUCT or SUMIFS?+
SUMIFS is faster and clearer for plain conditions. SUMPRODUCT handles OR logic, calculations inside the condition and weighted sums.
Why does my weighted average differ from AVERAGE?+
AVERAGE treats every row equally. A weighted average, SUMPRODUCT(values, weights) / SUM(weights), counts each row by its weight, like prices by quantity sold.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.