SUMPRODUCT is the most versatile formula in Excel. Here's what it can do that no other function can.
SUMPRODUCT multiplies corresponding elements in arrays together, then sums the results. When you pass TRUE/FALSE conditions, TRUE=1 and FALSE=0, so conditions act as filters.
=SUMPRODUCT(array1, array2, ...)
-- Multiplies element by element, then sums all products
-- Simple example:
=SUMPRODUCT({1,2,3}, {4,5,6})
-- = (1×4) + (2×5) + (3×6) = 4+10+18 = 32=SUMPRODUCT((A2:A100="North") * B2:B100)
-- Sum B where A="North"
=SUMPRODUCT((A2:A100="North") * (C2:C100>1000) * B2:B100)
-- Sum B where A="North" AND C>1000 (AND = multiply)
=SUMPRODUCT(((A2:A100="North")+(A2:A100="South")) * B2:B100)
-- Sum B where A="North" OR A="South" (OR = add, cap at 1)=SUMPRODUCT(values, weights) / SUM(weights)
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)
-- Weighted average of B using C as weights
-- Used for: weighted GPA, weighted price indices, portfolio returns=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
-- Count distinct values in A2:A100
-- How it works: COUNTIF returns how many times each value appears
-- 1/count gives a fraction, fractions for same value sum to 1
-- Total sum = number of unique values=SUMPRODUCT((B2:B100="North") / COUNTIFS(B2:B100,"North",A2:A100,A2:A100))
-- Count unique values in A where B="North"=SUMPRODUCT((ROW(A$2:A2)-ROW(A$2)+1<=ROW()-ROW(A$2)+1)*B$2:B2)
-- Running total using SUMPRODUCT
-- More complex than SUM with mixed reference but works in array contextsSUMPRODUCT is more flexible but slower than SUMIFS on large datasets. For AND conditions, use SUMIFS. Only use SUMPRODUCT when you need OR logic, calculated criteria, or unique counting that SUMIFS can't handle.
Practice these formulas in the Data Analyst track — 100 exercises covering dynamic arrays, data cleaning, and analysis. Free to start.
Start the Data Analyst track free →