FormulaCraft

SUMPRODUCT vs SUMIFS: weighted and conditional sums in Excel and Sheets

Topic:SUMIF & SUMIFS
Excel & Google Sheets
=SUMPRODUCT(A2:A5,B2:B5)
✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

QtyPrice
210
320
130
45

=SUMPRODUCT(A2:A5,B2:B5)→130

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

  1. 1SUMPRODUCT multiplies quantity by price row-by-row, then adds the results: 2×10 + 3×20 + 1×30 + 4×5 = 130.
  2. 2SUMIFS cannot multiply two columns — it only sums one column where criteria match.
  3. 3Reach for SUMIFS for conditional totals; reach for SUMPRODUCT for weighted totals or multi-condition math.

Tips

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

When must I use SUMPRODUCT over SUMIFS?

When you need to multiply arrays (quantity × price), or apply OR-logic across criteria that SUMIFS' AND-only model can't express.

More on SUMIF & SUMIFS

See all →
Reference

SUMIF

Add up the cells in a range that meet a single condition.

How-to

SUMIF vs SUMIFS: when to use each in Excel and Sheets

Understand when to use SUMIF (one condition) versus SUMIFS (multiple conditions) for conditional summing in Excel and Google Sheets.

Error fix

Fix 0

SUMIF returns 0 instead of the expected total in Excel and Google Sheets — fix number-as-text, criteria mismatch, range size errors, and data-type issues.

Reference

SUMIFS

Sum values that meet multiple conditions at once.

How-to

How to use SUMIF with a date range

Use SUMIF with a date comparison criterion to sum values only for dates on or after (or before) a specific date in your data range.

Error fix

Fix SUMIF

SUMIF not working in Excel and Google Sheets — diagnose formula errors, criteria syntax problems, range mismatches, and wildcard issues.

Formulas used

SUMPRODUCTSUMIFSSUMIFORVSTACKMAPDSUM

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: