SUMPRODUCT: The Excel Formula That Does (Almost) Everything

SUMPRODUCT: The Excel Formula That Does (Almost) Everything

📎 This article includes 2 downloadable practice files ↓

⏱ 2 min readUpdated 27 September 2026

Before dynamic arrays, SUMPRODUCT was the secret weapon of Excel experts, because it handles arrays without Ctrl+Shift+Enter. It still is useful in every version.

In this article
  1. The basic idea
  2. Counting and summing with conditions
  3. Things SUMIFS cannot do
  4. Continue learning

The basic idea

=SUMPRODUCT(B2:B10, C2:C10)     ' qty × price for each row, then added up

It multiplies the ranges row by row and sums the products — total sales value in one formula, no helper column.

Advertisement

Counting and summing with conditions

=SUMPRODUCT((A2:A100="North")*(C2:C100>=10))              ' count rows
=SUMPRODUCT((A2:A100="North")*(C2:C100>=10), D2:D100)       ' sum amounts

Each comparison gives TRUE/FALSE; multiplying turns them into 1/0. It works like COUNTIFS/SUMIFS but also accepts calculations inside the conditions.

Things SUMIFS cannot do

Need SUMPRODUCT
Condition on a month of a date =SUMPRODUCT((MONTH(B2:B100)=3)*D2:D100)
OR logic =SUMPRODUCT(((A2:A100="North")+(A2:A100="East")>0)*D2:D100)
Case-sensitive count =SUMPRODUCT(--EXACT(A2:A100,"ABC"))
Weighted average =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
Count unique values =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) (no blanks)
⚠️ All ranges must be the same size, or you get #VALUE!. Avoid whole columns (A:A) — SUMPRODUCT calculates every row and becomes slow.

The double minus -- converts TRUE/FALSE to 1/0 when there is nothing to multiply by. Paste any SUMPRODUCT into the Formula Explainer to see its parts.

⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Formula Combos guides →
Split Text by Comma, Dash or Any Delimiter in Excel (TEXTSPLIT, TEXTBEFORE, TEXTAFTER)Break “Mumbai-MH-400001” or comma lists into columns and rows with TEXTSPLIT, TEXTBEFORE and TEXTAFTER…Automatic Serial Numbers in Excel That Survive Filtering, Deleting and BlanksNumber rows 1, 2, 3 automatically: ROW and SEQUENCE for clean lists, COUNTA to…Combine Data From Multiple Sheets Into One in Excel (VSTACK, FILTER)Stack monthly or branch sheets into one live list with VSTACK and 3D references,…Split Full Names Into First, Middle and Last Name in ExcelSeparate "Asha Kumari Sharma" into first, middle and last names with TEXTBEFORE/TEXTAFTER/TEXTSPLIT, older LEFT/FIND/MID…

📎 Practice files for this article

⬇ Download all 2 files (ZIP · 43 KB)

  • 📗
    SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.
    ⬇ XLSX · 9 KB
  • 📗
    Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.
    ⬇ XLSX · 39 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong