How to Use SUMPRODUCT in Excel: Syntax, Conditions and Weighted Average

shape
shape
shape
shape
shape
shape
shape
shape
How to Use SUMPRODUCT in Excel: Syntax, Conditions and Weighted Average - featured image

SUMPRODUCT is one of the most useful functions in Excel, and one of the least understood. At its simplest, it multiplies the matching items of two or more lists and adds up the results, which is exactly what you need to calculate total revenue from units and prices in one cell. Used with conditions, it can sum and count with several criteria, calculate a weighted average and do many jobs that would otherwise need helper columns. This guide explains the syntax, works through examples with numbers that you can check by hand, shows how to add conditions, how to calculate a weighted average, how SUMPRODUCT compares with SUMIFS, and how to avoid the common errors.

Updated November 2026

In this guide

  1. What SUMPRODUCT does
  2. Example data
  3. Basic use: total revenue without a helper column
  4. Add a condition
  5. Use several conditions
  6. Count with conditions
  7. The double negative trick
  8. Calculate a weighted average
  9. SUMPRODUCT and weighted GPA
  10. SUMPRODUCT versus SUMIFS
  11. Compare two columns
  12. More practical uses
  13. Tips for readable formulas
  14. Common errors
  15. Practice problems
  16. Quick recap

What SUMPRODUCT does

The syntax is SUMPRODUCT(array1, [array2], [array3], …). Excel multiplies the first item of each array together, then the second item of each array, and so on, and adds all the products. The arrays must have the same number of rows and columns, or the function returns a #VALUE! error. If you give it only one array, it simply adds the numbers, like SUM. The word array just means a range of cells or a list of values. SUMPRODUCT works in all versions of Excel and does not need to be entered as an array formula.

Example data

Use a list of six sales lines in A2:C7 with the headers Region, Units and Price in row 1.

RegionUnitsPriceRevenue
North10550
South20480
North15690
East51050
South8540
East12336

The revenue column is shown only so that you can check the results. The total revenue is 50 plus 80 plus 90 plus 50 plus 40 plus 36, which is 346.

Basic use: total revenue without a helper column

To get total revenue, you would normally add a revenue column and sum it. With SUMPRODUCT you do it in one cell: =SUMPRODUCT(B2:B7,C2:C7). Excel calculates 10 times 5, 20 times 4, 15 times 6, 5 times 10, 8 times 5 and 12 times 3, which are 50, 80, 90, 50, 40 and 36, and adds them to give 346. This keeps the sheet tidy and the result always up to date. You can use three arrays as well, for example units times price times a tax factor.

Add a condition

To get the revenue of the North region only, multiply by a test that is TRUE for North rows: =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7). The test A2:A7=”North” returns TRUE or FALSE for each row, and multiplying converts them to 1 and 0. Only the North rows survive: 50 and 90. The result is 140. Use the same pattern for South, which gives 80 plus 40, which is 120, and East, which gives 50 plus 36, which is 86. The three regions together give 346, matching the total.

Use several conditions

To combine conditions with AND, multiply the tests. For the revenue of South lines with a price of at least 5, use =SUMPRODUCT((A2:A7="South")*(C2:C7>=5)*B2:B7*C2:C7). Only the second South line passes, which has 8 units at a price of 5, so the result is 40. To combine conditions with OR, add the tests inside brackets. For North or East revenue, use =SUMPRODUCT(((A2:A7="North")+(A2:A7="East"))*B2:B7*C2:C7), which gives 140 plus 86, which is 226. Always use brackets around each test.

Count with conditions

SUMPRODUCT can also count. To count the North lines with more than 12 units, use =SUMPRODUCT((A2:A7="North")*(B2:B7>12)). The North lines have 10 and 15 units, so only one passes, and the result is 1. This is like COUNTIFS, but it allows tests that COUNTIFS cannot do, such as comparing two columns or testing the length of text. Our COUNTIFS guide covers the standard way, which is simpler for common cases.

The double negative trick

You may see formulas written with two minus signs, such as =SUMPRODUCT(--(A2:A7="North"),B2:B7). The double negative converts TRUE and FALSE to 1 and 0, in the same way as multiplication does. In this example, the formula adds the units of the North lines, which is 10 plus 15, so 25. The comma form with two arrays has an advantage: SUMPRODUCT treats text in an array as zero, so it does not return an error. Both styles give the same result, so use the one that you find easier to read.

Calculate a weighted average

A weighted average gives more importance to some values. SUMPRODUCT is the classic tool for it. Suppose scores of 80, 90 and 70 have weights of 50, 30 and 20 percent. Put the scores in B2:B4 and the weights in C2:C4. Then =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) gives 80 times 0.5 plus 90 times 0.3 plus 70 times 0.2, which is 40 plus 27 plus 14, or 81. For our sales data, the average price weighted by units is =SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7), which is 346 divided by 70, or 4.94. Our weighted average guide explains the idea in detail.

SUMPRODUCT and weighted GPA

The same formula is used for a grade point average with credits. Put the credits in one column and the grade points in another, then divide the SUMPRODUCT by the total credits. This is the method in our weighted GPA guide. It shows how one function covers many different tasks, since a weighted average is the same calculation whatever the numbers represent, whether prices and units, scores and weights, or grades and credits.

SUMPRODUCT versus SUMIFS

SUMIFS is faster and easier for a simple sum with conditions, such as the total units for North, with =SUMIFS(B2:B7,A2:A7,"North"), which gives 25. SUMPRODUCT is better when the result needs a multiplication of two columns, since SUMIFS can only add a single column. It is also better for complex conditions, such as OR logic across two columns. On very large data sets, SUMPRODUCT can be slower, because it processes whole arrays, so use SUMIFS when it can do the job. Our SUMIFS guide explains it fully.

Compare two columns

SUMPRODUCT can count the rows where two columns match. If column A holds planned values and column B holds actual values, =SUMPRODUCT(--(A2:A7=B2:B7)) counts the rows where they are equal. To count the rows where the actual is above the plan, use =SUMPRODUCT(--(B2:B7>A2:A7)). This is a quick way to check reconciliations, and to count how many targets were met. The arrays must have the same size, otherwise you get #VALUE!.

More practical uses

SUMPRODUCT suits many everyday tasks. For an inventory sheet, the stock value is the SUMPRODUCT of quantity and cost, so with quantities of 10, 20 and 5 and costs of 3, 2 and 8, the value is 30 plus 40 plus 40, which is 110. For a budget, you can multiply hours by an hourly rate for each person and get the total labor cost in one cell. For a survey, you can count the answers that meet two conditions at once. Because the formula refers to whole ranges, new rows inside the range are included automatically, and if you use a table, the formula grows with the data.

Tips for readable formulas

Long SUMPRODUCT formulas can be hard to read, so use named ranges or table columns. For example, =SUMPRODUCT((Region="North")*Units*Price) is easier to understand than a version with cell addresses. Write each condition in its own bracket, and add a short comment in a nearby cell that says what the formula does. Test the formula first with a small data set, as in this guide, so you know the expected answer before you use it on thousands of rows. Our named range guide explains how to create the names.

Common errors

The most common error is #VALUE!, caused by arrays of different sizes, such as B2:B7 and C2:C6. Check that each range has the same number of rows. Another problem is numbers stored as text, which SUMPRODUCT treats as zero in the comma form, so the result is too low. Convert them first, as shown in our text to number guide. A third problem is forgetting brackets around each condition, which gives wrong results because of the order of operations. A fourth is using whole columns, which slows the sheet.

Practice problems

Try these with the sales data. Problem 1: total revenue is 346. Problem 2: revenue for South is 120. Problem 3: how many lines have a price above 5? There are two, which are 6 and 10. Problem 4: what is the weighted average price? It is 346 divided by 70, or 4.94. Problem 5: write a formula for the revenue of East lines with more than 6 units, which is the line with 12 units at a price of 3, so 36.

Quick recap

SUMPRODUCT multiplies matching items in two or more arrays and adds the results. Use it for totals such as units times price without a helper column, add conditions by multiplying tests in brackets, and use it to count with several criteria. It is the standard tool for a weighted average. Make sure the arrays have the same size, keep numbers as numbers, and use SUMIFS when a simple conditional sum is enough. With SUMPRODUCT, many multi-step calculations become a single readable formula.

Frequently asked questions

What does SUMPRODUCT do in Excel?

It multiplies matching items in arrays and adds the products.

Why does SUMPRODUCT return #VALUE!?

The arrays have different sizes.

Can SUMPRODUCT have conditions?

Yes, multiply tests that return TRUE or FALSE inside brackets.

How do I calculate a weighted average with SUMPRODUCT?

Divide the SUMPRODUCT of values and weights by the sum of the weights.

Is SUMPRODUCT slower than SUMIFS?

It can be slower on large data, so use SUMIFS when it does the job.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *