Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 93 ยท Logical and Conditional Formulas

SUMPRODUCT and Array-Style Calculations

Learn SUMPRODUCT in Excel: multiply and add ranges, compute weighted averages, count with several conditions, and use boolean arrays and the double negative.

SUMPRODUCT has an odd name and a humble job: it multiplies matching cells in two or more ranges and adds up the products. That is exactly what you need for "quantity times price, summed", and for weighted averages. But SUMPRODUCT has a second life as the classic way to do array-style calculations in a single cell, such as counting rows that meet several conditions, long before dynamic arrays existed. It handles whole arrays natively, with no special key presses, in every Excel version.

You should be comfortable with ranges and with AND/OR and booleans as numbers. The formulas are real Excel; the live examples run on a JavaScript stand-in engine that evaluates ranges and arrays element by element (the real function is much faster and has more edge cases), plus pandas for the Python view.

What SUMPRODUCT does

Multiply Pair up cells in each range.
Add Total all the products.
Same size Every range must match in shape.

=SUMPRODUCT(array1, [array2], ...)

1 / 2

The basic form

TEXT
=SUMPRODUCT(C2:C7, D2:D7)

With ranges C2:C7 (quantities) and D2:D7 (prices), Excel computes C2*D2 + C3*D3 + ... + C7*D7. That gives total revenue without a helper column of per-row totals. All ranges must have the same number of rows and columns, otherwise the result is #VALUE!. Cells that are text or empty count as zero in this comma form.

Weighted averages

An ordinary average treats every value equally. When items count differently, such as an exam worth more than a quiz, divide the weighted total by the total weight:

TEXT
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
JS Totals and weighted averages with SUMPRODUCT

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The weighted grade is 77 while the plain average is 80, because the heaviest item (weight 5) has the lowest score. Two lines at the bottom show a subtle difference: with a text cell, the comma form quietly treats "two" as 0 and returns 4, while multiplying inside one argument (G1:G3*H1:H3) makes Excel try real arithmetic on the text and fail with #VALUE!.

Conditions inside SUMPRODUCT

The powerful trick: a comparison on a range produces an array of TRUE/FALSE values, one per cell. Since TRUE is 1 and FALSE is 0 in arithmetic, multiplying such arrays is an AND, and adding them is an OR:

TEXT
=SUMPRODUCT((A2:A7="East") * C2:C7 * D2:D7)         East revenue
=SUMPRODUCT(--(A2:A7="East"))                       how many East rows
=SUMPRODUCT((A2:A7="East") * (B2:B7="Book"))        East AND Book
=SUMPRODUCT(--((A2:A7="East")+(A2:A7="West")>0))    East OR West

In the first formula each row yields 1*qty*price for East rows and 0*qty*price for all other rows, so only East revenue survives the sum.

Why the double negative

In the comma form, SUMPRODUCT ignores logical values: SUMPRODUCT(A2:A7="East") returns 0, because TRUE and FALSE are not numbers. The double unary -- (a minus twice) turns them into 1 and 0. Multiplication does the same conversion automatically, which is why (test)*values needs no --. Use -- when the array stands alone, and * when you already multiply.

JS Multi-condition counting with boolean arrays

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice the last two lines. Without -- the answer is 0 even though three rows match, and the lowercase "east" still matches three rows because Excel comparisons ignore case.

Strengths and limits

SUMPRODUCT is flexible, but it is not always the right tool:

  • Prefer SUMIFS/COUNTIFS when they fit. They are simpler to read and faster on big ranges, and they support wildcards ("Pen*"). See SUMIF, COUNTIF and the IFS family. Use SUMPRODUCT for what they cannot do: OR conditions across different columns, calculations on the values (LEN(A2:A100)>5, MONTH(B2:B100)=3), or weighted math.
  • Avoid whole-column references. SUMPRODUCT computes every cell, so A:A means over a million cells per formula. Use exact ranges or an Excel Table.
  • Keep the shapes equal. One range of 6 rows and one of 5 rows gives #VALUE!.
  • Dynamic arrays changed the story. In Microsoft 365, formulas such as =SUM((A2:A7="East")*C2:C7) work directly, but in older versions they needed Ctrl+Shift+Enter. SUMPRODUCT never needed it, which is why it remains common in files shared across versions. See Dynamic Arrays and Spilling.

The same ideas in pandas

In pandas a boolean mask plays the role of the comparison array, and multiplying columns plays the role of SUMPRODUCT:

Python SUMPRODUCT patterns with pandas and NumPy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =SUMPRODUCT(B2:B4, C2:C4) calculate?

    SUMPRODUCT multiplies matching cells of each range and adds the products.
  2. 2. Which formula gives the weighted average of scores in B2:B4 with weights in C2:C4?

    Divide the weighted total by the total weight. Without the division you get the weighted sum, not the average.
  3. 3. Why does =SUMPRODUCT(A2:A7="East") return 0 even when some cells equal East?

    The array of TRUE/FALSE values must be converted to 1 and 0, for example with a double unary (--) or by multiplying.
  4. 4. Two ranges of different sizes are passed to SUMPRODUCT. What happens?

    All arguments must have the same dimensions, otherwise the result is #VALUE!.
  5. 5. When is SUMIFS a better choice than SUMPRODUCT?

    SUMIFS and COUNTIFS are simpler and faster for plain AND conditions and support wildcards. SUMPRODUCT covers the cases they cannot.

Summary

  • SUMPRODUCT(array1, array2, ...) multiplies matching cells and adds the products; all arrays must have the same shape.
  • A weighted average is SUMPRODUCT(values, weights) / SUM(weights).
  • Comparing a range gives an array of TRUE/FALSE; multiply arrays for AND, add them for OR, and use -- to turn a lone boolean array into 1s and 0s.
  • In the comma form text and logical values count as 0; inside one argument, arithmetic on text gives #VALUE!.
  • Prefer SUMIFS/COUNTIFS for simple AND conditions; use exact ranges for speed.

Next: Handling Errors and Blanks in Formulas.