Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
SUM vs SUMPRODUCT for conditional totals
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT((A2:A6="Alice")*(B2:B6))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1For a simple total, use =SUM(B2:B6). For a conditional total, use =SUMIF(A2:A6,"Alice",B2:B6).
- 2For multiple conditions, use =SUMIFS(B2:B6, A2:A6,"Alice", C2:C6,"Active") — SUMIFS is cleaner for up to about three conditions.
- 3For complex or computed conditions, use SUMPRODUCT: =SUMPRODUCT((A2:A6="Alice")*(B2:B6)) — each condition in parentheses returns a 1/0 array that acts as a filter.
- 4Combine multiple conditions: =SUMPRODUCT((A2:A6="Alice")*(B2:B6>100)*(B2:B6)) sums column B where A is Alice AND B is over 100.
Tips
- SUMPRODUCT is slower on very large ranges than SUMIFS — use SUMPRODUCT for flexibility and computed criteria, SUMIFS for performance on large datasets.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can SUMPRODUCT replace SUMIFS entirely?
SUMPRODUCT can replicate all SUMIFS behaviour and more, but SUMIFS is faster on large ranges and more readable for simple criteria. Use each where it fits best.
Why does SUMPRODUCT return 0 instead of the expected total?
The most common cause is a text-stored number in the sum column. Multiply by 1 inside the formula (B2:B6*1) to coerce text numbers to values.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.