formulastreak

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.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = $397.25
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusRevenue$397.25
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

Conditional totals

Conditions become 1s and 0s when multiplied, so SUMPRODUCT((B="North")*(C="Fern")*E) adds only the matching rows, like SUMIFS.

Monthly sales by region · edit H1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH1 = $1,738.50
ABCDEFGH
1MonthRegionProductUnitsRevenueNorth Fern$1,738.50
2Jan 2026NorthFern40$380.00
3Jan 2026NorthMonstera12$408.00
4Jan 2026SouthFern35$332.50
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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.

Monthly sales by region · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = $1,738.50
ABCDEFGHIJ
1MonthRegionProductUnitsRevenueRevenueFernMonstera
2Jan 2026NorthFern40$380.00North$1,738.50$2,006.00
3Jan 2026NorthMonstera12$408.00South$1,387.00$1,666.00
4Jan 2026SouthFern35$332.50
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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.

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = Lina Chen
ABCDEFGH
1RepRegionJulAugSepTargetRankRep
2Amara OseiNorth$12,400$13,100$11,800$36,0001Lina Chen
3Ben FischerSouth$9,800$10,250$12,100$30,0002Hugo Martin
4Lina ChenEast$15,200$14,800$16,900$45,0003Farid Haddad
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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
Select I2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHIJ
1MonthRegionProductUnitsRevenueUnitsFernMonstera
2Jan 2026NorthFern40$380.00North
3Jan 2026NorthMonstera12$408.00South
4Jan 2026SouthFern35$332.50
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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.

Practise it now