∑ifConditional math20 min read ·

How to Use SUMPRODUCT in Excel

Learn SUMPRODUCT with real examples. Multiply and add, weighted averages, sums and counts with AND and OR criteria, months, case-sensitive matches and errors.

Quick answer

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

SUMPRODUCT multiplies matching items in two or more ranges and adds up the results, like =SUMPRODUCT(E2:E11,G2:G11) for units times price. Turn a condition into 1s and 0s, as in --(D2:D11="Monitor"), and it adds up or counts only the rows that match.

Arguments in [brackets] are optional.

On this page ⌄
  1. The SUMPRODUCT syntax
  2. A basic SUMPRODUCT example: units times price
  3. Calculate a weighted average with SUMPRODUCT
  4. How SUMPRODUCT handles conditions
  5. What the double minus (--) does
  6. SUMPRODUCT with multiple criteria (AND)
  7. SUMPRODUCT with OR conditions
  8. Avoid double counting with OR
  9. Count rows with conditions
  10. Sum by month, year or weekday
  11. Case-sensitive sums and counts with EXACT
  12. Partial matches: SEARCH instead of wildcards
  13. Multiply and filter in one step
  14. SUMPRODUCT vs SUMIFS: which to use
  15. Fix #VALUE! and other SUMPRODUCT errors
  16. SUMPRODUCT in Microsoft 365: SUM works too
  17. Common mistakes
  18. Frequently asked questions about SUMPRODUCT
  19. Which versions of Excel have SUMPRODUCT?
  20. Key takeaways

To multiply two columns row by row and add up the results, use SUMPRODUCT. Give it the ranges, separated by commas. Turn a condition into 1s and 0s, and the same function adds up only the rows you want:

=SUMPRODUCT(E2:E11, G2:G11)→ $18,700
=SUMPRODUCT((D2:D11="Monitor")*F2:F11)→ $5,500

The first formula multiplies each order's units by its price and adds up the ten results, which gives the total revenue. The second adds up the revenue of the monitor orders only. This guide covers both uses: the basic multiply-and-add, weighted averages, sums and counts with AND and OR conditions, and the conditions SUMIFS can't handle, such as a month, a weekday or a case-sensitive match. It also covers when SUMIFS is the better choice, the errors SUMPRODUCT gives and what changed with dynamic arrays in Microsoft 365.

The examples use the sales log from the SUMIF and SUMIFS guide, with one extra column, G, for the price of one unit:

A: DateB: RegionC: RepD: ProductE: UnitsF: RevenueG: Price
2Jan 4, 2026NorthAvaLaptop3$3,600$1,200
3Jan 9, 2026SouthLeoMonitor5$1,250$250
4Jan 15, 2026NorthMayaLaptop2$2,400$1,200
5Jan 22, 2026EastSamKeyboard12$720$60
6Feb 3, 2026WestZoeMonitor4$1,000$250
7Feb 11, 2026NorthAvaKeyboard8$480$60
8Feb 19, 2026SouthIvyLaptop4$4,800$1,200
9Feb 27, 2026EastSamMonitor6$1,500$250
10Mar 6, 2026WestNoahLaptop1$1,200$1,200
11Mar 14, 2026NorthMayaMonitor7$1,750$250

The SUMPRODUCT syntax

=SUMPRODUCT(array1, [array2], [array3], ...)
ArgumentWhat it meansExample
array1The first range or array. On its own, SUMPRODUCT just adds it upE2:E11
array2, array3, ...Optional. More ranges or arrays the same size as array1, up to 255 in allG2:G11

SUMPRODUCT multiplies the first items of every array together, then the second items, then the third, and so on. Then it adds up all those products. Microsoft's documentation adds two rules that explain most of what follows:

  • Every array must be the same size. =SUMPRODUCT(E2:E11, G2:G10) returns #VALUE!, because one range has ten cells and the other has nine.
  • Entries that aren't numbers, such as text and TRUE or FALSE, count as zero. That's why conditions need the double minus or a multiplication.

SUMPRODUCT also works through arrays by itself, so it doesn't need Ctrl+Shift+Enter in any version of Excel. That's what made it the classic tool for sums with conditions.

A basic SUMPRODUCT example: units times price

What's the total revenue, worked out from the units and the prices?

=SUMPRODUCT(E2:E11, G2:G11)→ $18,700

SUMPRODUCT multiplies the two columns row by row: 3 × 1,200 for Ava's laptops, 5 × 250 for Leo's monitors, and so on down to 7 × 250 in row 11. Then it adds up the ten results. It's a shorter way of writing this:

=E2*G2+E3*G3+E4*G4+E5*G5+E6*G6+E7*G7+E8*G8+E9*G9+E10*G10+E11*G11→ $18,700

Column F already holds each row's units times price, so =SUM(F2:F11) also returns $18,700. That's the point of SUMPRODUCT: it gets the total without a helper column of multiplications.

You can also multiply the ranges yourself and give SUMPRODUCT a single array:

=SUMPRODUCT(E2:E11*G2:G11)→ $18,700

Both forms give the same answer on clean data. They behave differently when a range holds text, as the errors section shows, and Microsoft's performance guidance says the comma form calculates a little faster.

Calculate a weighted average with SUMPRODUCT

A weighted average gives some values more say than others. What did a unit sell for, on average? AVERAGE gets this wrong:

=AVERAGE(G2:G11)→ $592

AVERAGE gives every order's price the same weight. But the four laptop orders, at $1,200 each, add up to only 10 units, while the monitors and keyboards make up the other 42. To weight each price by the units sold, multiply them with SUMPRODUCT and divide by the total units:

=SUMPRODUCT(E2:E11, G2:G11) / SUM(E2:E11)→ $359.62

That's $18,700 of revenue from 52 units. The pattern is always the same: SUMPRODUCT of the values and the weights, divided by the SUM of the weights.

When the weights already add up to 100%, you can skip the division. Say a gradebook has each student's Math, English and Science scores in columns B to D, starting with Emma in row 2, and the weights 50%, 30% and 20% in B12:D12. Emma's weighted grade is:

=SUMPRODUCT(B2:D2, $B$12:$D$12)→ 83.7

Emma scored 82, 91 and 77, so that's 41 + 27.3 + 15.4. The ranges go across a row instead of down a column, which works as long as both have the same shape. The dollar signs lock the weights so you can copy the formula down to the other students. The absolute vs relative references guide explains why.

How SUMPRODUCT handles conditions

A comparison such as D2:D11="Monitor" checks every cell in the range and returns a list of TRUE and FALSE values, one for each row. When you multiply that list by a column of numbers, TRUE acts as 1 and FALSE as 0. Every row that fails the test becomes zero, so only the matching rows are left to add up:

=SUMPRODUCT((D2:D11="Monitor")*F2:F11)→ $5,500

Here's what happens, row by row:

RowD: ProductD2:D11="Monitor"× F: Revenue=
2LaptopFALSE$3,6000
3MonitorTRUE$1,2501,250
4LaptopFALSE$2,4000
5KeyboardFALSE$7200
6MonitorTRUE$1,0001,000
7KeyboardFALSE$4800
8LaptopFALSE$4,8000
9MonitorTRUE$1,5001,500
10LaptopFALSE$1,2000
11MonitorTRUE$1,7501,750

SUMPRODUCT adds up the last column: 1,250 + 1,000 + 1,500 + 1,750. That's the same answer =SUMIF(D2:D11, "Monitor", F2:F11) gives. To watch the steps in your own formula, select the cell and click Formulas > Evaluate Formula.

The condition is an ordinary comparison, so it's written differently from a SUMIF condition. The operator goes outside the quotes, and a cell needs no &. If H2 holds South, these return the South revenue and the revenue from orders of 5 units or more:

=SUMPRODUCT((B2:B11=H2)*F2:F11)→ $6,050
=SUMPRODUCT((E2:E11>=5)*F2:F11)→ $5,700

<> means not equal, so =SUMPRODUCT((B2:B11<>"East")*F2:F11) returns $16,480, the revenue from every region except East. Like SUMIF, the comparison ignores upper and lower case.

What the double minus (--) does

In the comma form, SUMPRODUCT treats TRUE and FALSE as zero, just like text. So a condition on its own as an argument gives 0:

=SUMPRODUCT((D2:D11="Monitor"), F2:F11)→ 0

The fix is two minus signs in front of the condition. The first minus turns TRUE into -1 and FALSE into 0, and the second turns -1 back into 1:

=SUMPRODUCT(--(D2:D11="Monitor"), F2:F11)→ $5,500

The multiplication in the previous section did the same job, because any arithmetic turns TRUE and FALSE into numbers. (D2:D11="Monitor")*1 and (D2:D11="Monitor")+0 work too, but -- is the usual way to write it, and Microsoft's performance guidance says it's slightly faster. You only need it when a condition stands alone as an argument. Once you multiply a condition by something, it's already a number.

So which form should you use? On clean data they give the same result. The comma form is a little faster, and it treats text in the range to add up as zero instead of returning an error. The multiplication form is shorter to write with several conditions, and it's the form Microsoft's own examples use. This guide uses both.

Your turn. The sheet below is live and uses the same sales log, without the Price column. This exercise checks for SUMIF or SUMIFS, so write it that way: the range to check, the condition, then the range to add up. Those are the same three pieces as in the SUMPRODUCT formulas above, in a different order.

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

Exercise 1 of 11SUMIF

What was the total revenue from the North region?

Write your formula in the green cell, I2.

q1-orders.xlsx
fx
1DateRegionRepProductUnitsRevenue
2Jan 4, 2026NorthAvaLaptop3$3,600.00North revenue
3Jan 9, 2026SouthLeoMonitor5$1,250.00
4Jan 15, 2026NorthMayaLaptop2$2,400.00
5Jan 22, 2026EastSamKeyboard12$720.00
6Feb 3, 2026WestZoeMonitor4$1,000.00
7Feb 11, 2026NorthAvaKeyboard8$480.00
8Feb 19, 2026SouthIvyLaptop4$4,800.00
9Feb 27, 2026EastSamMonitor6$1,500.00
10Mar 6, 2026WestNoahLaptop1$1,200.00
11Mar 14, 2026NorthMayaMonitor7$1,750.00
12
13

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

For a single condition like that one, SUMIF is the simpler tool. SUMPRODUCT earns its place as the conditions get more complicated, which is where the rest of this guide goes.

SUMPRODUCT with multiple criteria (AND)

To add up a row only when every condition is true, multiply the conditions. A row is left with 1 only when all of them are TRUE: 1 × 1 is 1, but 1 × 0 and 0 × 0 are both 0. How much laptop revenue came from the North?

=SUMPRODUCT((B2:B11="North")*(D2:D11="Laptop")*F2:F11)→ $6,000

Two rows pass both tests, Ava's $3,600 and Maya's $2,400. That's the same answer as =SUMIFS(F2:F11, B2:B11, "North", D2:D11, "Laptop"). In the comma form, give each condition its own --:

=SUMPRODUCT(--(B2:B11="North"), --(D2:D11="Laptop"), F2:F11)→ $6,000

Two conditions can test the same column. Here's the revenue from orders with 3 to 6 units:

=SUMPRODUCT((E2:E11>=3)*(E2:E11<=6)*F2:F11)→ $12,150

Five orders qualify, with 3, 5, 4, 4 and 6 units.

SUMPRODUCT with OR conditions

For OR, add the conditions instead of multiplying them. A row then gets 1 if either test is TRUE. What was the revenue from monitors or keyboards?

=SUMPRODUCT(((D2:D11="Monitor")+(D2:D11="Keyboard"))*F2:F11)→ $6,700

That's $5,500 from monitors plus $1,200 from keyboards. Notice the extra brackets around the two conditions. Excel multiplies before it adds, so without them, =SUMPRODUCT((D2:D11="Monitor")+(D2:D11="Keyboard")*F2:F11) multiplies only the keyboard test by the revenue. It returns 1,204: the $1,200 of keyboard revenue plus 1 for each of the four monitor rows.

Avoid double counting with OR

A product can't be a monitor and a keyboard at the same time, so in the example above each row gets at most 1. When the conditions are on different columns, a row can pass both tests, and adding them gives it a 2. What was the revenue from West orders or laptop orders?

=SUMPRODUCT(((B2:B11="West")+(D2:D11="Laptop"))*F2:F11)→ $14,200

That's too much. Noah's $1,200 laptop order in row 10 is from the West, so it gets TRUE + TRUE = 2 and is added twice. To count every matching row once, test whether the total is greater than 0:

=SUMPRODUCT((((B2:B11="West")+(D2:D11="Laptop"))>0)*F2:F11)→ $13,000

>0 turns the 1s and 2s into TRUE and the 0s into FALSE, and the multiplication turns those back into 1s and 0s. That's the right total: Zoe's $1,000 monitor order plus the four laptop orders, which come to $12,000.

Count rows with conditions

Leave out the range to add up, and SUMPRODUCT counts the matching rows instead, because every row that passes is a 1. A single condition needs --:

=SUMPRODUCT(--(D2:D11="Monitor"))→ 4

With two or more conditions, the multiplication already turns them into numbers. How many monitor orders had at least 5 units?

=SUMPRODUCT((D2:D11="Monitor")*(E2:E11>=5))→ 3

That's Leo, Sam and Maya. =COUNTIFS(D2:D11, "Monitor", E2:E11, ">=5") returns 3 as well, and for counts like this the COUNTIF and COUNTIFS functions are shorter. SUMPRODUCT is for the counts they can't do, like an OR across two columns:

=SUMPRODUCT(--(((B2:B11="West")+(D2:D11="Laptop"))>0))→ 5

Without the >0, =SUMPRODUCT((B2:B11="West")+(D2:D11="Laptop")) returns 6, counting Noah's order twice.

Sum by month, year or weekday

SUMIFS can only test the values in a range as they are. You can't wrap the range in a function, as in =SUMIFS(F2:F11, MONTH(A2:A11), 2), and Excel won't accept the formula. SUMPRODUCT works with any calculated array, so it can test the month directly. What was February's revenue?

=SUMPRODUCT((MONTH(A2:A11)=2)*F2:F11)→ $7,780

MONTH returns the month number of every date, and the four February orders pass the test. The SUMIF guide gets the same $7,780 with two date conditions. MONTH ignores the year, so once your data covers more than one year, add a YEAR test:

=SUMPRODUCT((YEAR(A2:A11)=2026)*(MONTH(A2:A11)=2)*F2:F11)→ $7,780

The weekday works the same way. WEEKDAY with 2 as its second argument numbers the days from Monday = 1 to Sunday = 7, so Saturday and Sunday are the days greater than 5. How much revenue came from weekend orders?

=SUMPRODUCT((WEEKDAY(A2:A11, 2)>5)*F2:F11)→ $5,350

January 4, 2026 was a Sunday and March 14 was a Saturday, so that's Ava's $3,600 plus Maya's $1,750.

Case-sensitive sums and counts with EXACT

SUMIFS, COUNTIFS and the = sign all ignore case, so "monitor" matches "Monitor". When case matters, as with product codes like ab-1 and AB-1, use EXACT. It compares two pieces of text and returns TRUE only when they match exactly, capitals included:

=SUMPRODUCT(EXACT(D2:D11, "Monitor")*F2:F11)→ $5,500

On this data it returns the same $5,500 as before. But say someone typed "monitor" in lower case in D6. =SUMIF(D2:D11, "Monitor", F2:F11) would still return $5,500, while the EXACT version would return $4,500, because Zoe's $1,000 order no longer matches. To count instead, put -- in front: =SUMPRODUCT(--EXACT(D2:D11, "Monitor")) returns 4 on the original data.

Partial matches: SEARCH instead of wildcards

Inside SUMPRODUCT, = compares text exactly as written, so wildcards don't work. D2:D11="*board" looks for a product actually called *board, finds nothing and adds up to 0. To test whether a cell contains some text, use SEARCH. It returns the position of the text when it finds it and #VALUE! when it doesn't, so ISNUMBER turns the result into TRUE or FALSE:

=SUMPRODUCT(ISNUMBER(SEARCH("board", D2:D11))*F2:F11)→ $1,200

That's the two keyboard orders, $720 and $480. SUMIFS can do this one too, with the wildcard condition "*board*". The SEARCH version is for what wildcards can't do. Swap SEARCH for FIND and the match becomes case-sensitive. You can also add several partial matches together for an OR, with >0 around them if one cell could match more than one. The LEFT, RIGHT and MID guide explains the difference between FIND and SEARCH.

Multiply and filter in one step

SUMIFS can only add up a column that already exists. SUMPRODUCT can multiply columns and apply conditions at the same time, which none of the IFS functions can do. Here's the laptop revenue, worked out from units and prices without using column F:

=SUMPRODUCT((D2:D11="Laptop")*E2:E11*G2:G11)→ $12,000
=SUMPRODUCT(--(D2:D11="Laptop"), E2:E11, G2:G11)→ $12,000

Each laptop row's units are multiplied by its price, every other row becomes zero, and the results are added up: 3,600 + 2,400 + 4,800 + 1,200. This is the formula to reach for when a table has quantities and prices but no totals column.

SUMPRODUCT vs SUMIFS: which to use

SUMIFSSUMPRODUCT
AND conditionsYesYes, multiply them
OR conditionsAdd several SUMIFS, which can double count across columnsAdd the conditions, with >0 across columns
Functions on the range, such as MONTH, YEAR or TRIMNoYes
Multiply columns before addingNoYes
WildcardsYesNo, use ISNUMBER and SEARCH
Case-sensitive matchesNoYes, with EXACT or FIND
Text in the range to add upSkippedZero in the comma form, #VALUE! when multiplied
Whole columns like F:FCalculated efficientlyEvery cell is calculated, so avoid them
Speed on large dataMuch fasterSlower
Ranges in a closed workbook#VALUE!Works

Use SUMIFS when it can do the job. Microsoft's Excel performance tips recommend SUMIFS, COUNTIFS and AVERAGEIFS over SUMPRODUCT wherever possible, because they're much faster to calculate. On a few hundred rows you won't notice the difference. Thousands of SUMPRODUCT formulas over long ranges can make a workbook slow to recalculate.

Use SUMPRODUCT when SUMIFS can't do it: an OR across columns, a function on the range, a case-sensitive match or columns that need multiplying first. It also keeps working when its ranges are in another workbook that's closed. Microsoft documents that SUMIF and SUMIFS return #VALUE! in that case until you open the source workbook.

Fix #VALUE! and other SUMPRODUCT errors

Most SUMPRODUCT errors come from the size of the ranges or from what's in them. The comma form and the multiplication form react differently:

What's in the rangesComma form, such as =SUMPRODUCT(--(D2:D11="Monitor"), F2:F11)Multiplication form, such as =SUMPRODUCT((D2:D11="Monitor")*F2:F11)
Text, such as TBCCounted as 0#VALUE!
A number stored as textCounted as 0Converted to a number
An empty cellCounted as 0Counted as 0
The header rowCounted as 0#VALUE!
An error, such as #N/AThe same errorThe same error
Ranges of different sizes#VALUE!#N/A

Ranges of different sizes. In the comma form, SUMPRODUCT checks the sizes and returns #VALUE!. In the multiplication form, Excel does the multiplication first and fills the missing cells of the shorter range with #N/A, so that's the error you see:

=SUMPRODUCT(E2:E11, G2:G10)→ #VALUE!
=SUMPRODUCT(E2:E11*G2:G10)→ #N/A

Either way, the fix is to start and end every range on the same rows.

Text in a range. Say Zoe's revenue in F6 hasn't been confirmed, and the cell holds the text TBC:

=SUMPRODUCT((D2:D11="Monitor")*F2:F11)→ #VALUE!
=SUMPRODUCT(--(D2:D11="Monitor"), F2:F11)→ $4,500

The multiplication form tries to work out TRUE × "TBC", which is #VALUE!, and one error is enough to break the whole formula. It would fail even if TBC were in a row that doesn't match, because FALSE × "TBC" is an error too. The comma form hands the text to SUMPRODUCT, which counts it as zero, so the other three monitor orders add up to $4,500. SUMIFS skips text as well and returns $4,500. The same thing happens with headers: if a range starts in row 1 by mistake, =SUMPRODUCT(E1:E11*G1:G11) returns #VALUE!, because Excel tries to multiply the word Units by the word Price.

Numbers stored as text. These cut the other way, and they often arrive with data from other systems. If the price in G6 were the text "250" instead of the number, =SUMPRODUCT(E2:E11, G2:G11) would return $17,700. It quietly counts Zoe's $1,000 order as zero. The multiplication form converts text that looks like a number, so =SUMPRODUCT(E2:E11*G2:G11) would still return $18,700. The real fix is to convert the text to numbers.

Errors in the data. If any cell in the ranges holds an error, such as #N/A from a lookup, SUMPRODUCT returns that error in both forms. Fix it where it starts, for example by wrapping the lookup in IFERROR so it returns 0.

SUMPRODUCT in Microsoft 365: SUM works too

In Excel 2019 and earlier, SUM can't work through a calculation like (B2:B11="South")*F2:F11 unless you enter it as an array formula with Ctrl+Shift+Enter. Without that, it returns a wrong number or #VALUE!. SUMPRODUCT always could, which is why it became the standard tool for sums with conditions. In Microsoft 365 and Excel 2021 or later, every formula works through arrays, so SUM does the same job when you just press Enter:

=SUM((B2:B11="South")*F2:F11)→ $6,050
=SUMPRODUCT((B2:B11="South")*F2:F11)→ $6,050

If everyone who opens the workbook has Microsoft 365, use whichever you find easier to read. If some people still have an older version, SUMPRODUCT is the safe choice, because it works the same everywhere. One exception: in Excel 2019 and earlier, IF inside SUMPRODUCT still needs Ctrl+Shift+Enter. Write the condition as a multiplication, as this guide does, and you never need IF.

Microsoft 365 also offers FILTER: keep the matching rows, then add them up:

=SUM(FILTER(F2:F11, MONTH(A2:A11)=2))→ $7,780

When no row matches, FILTER returns #CALC!, and so does the SUM around it. SUMPRODUCT simply returns 0. To make the FILTER version return 0 too, give FILTER a third argument: =SUM(FILTER(F2:F11, MONTH(A2:A11)=2, 0)).

Common mistakes

ProblemWhat's going onFix
Result is 0A condition stands alone in the comma form, so TRUE and FALSE count as zeroPut -- in front of it, or multiply it by the range
Result is 0 with * or ?= doesn't understand wildcardsUse ISNUMBER(SEARCH("text", D2:D11))
Result is 0 or too lowExtra spaces in the data, or numbers stored as text in the comma formClean the data with TRIM, or test TRIM(B2:B11)="South", and convert text to numbers
#VALUE!The ranges are different sizes, or a multiplied range holds text or a headerStart and end every range on the same rows, or use the comma form
#VALUE! from a conditionThe comparison isn't in brackets, so Excel multiplies "Monitor"*F2:F11 firstWrite (D2:D11="Monitor")*F2:F11
#N/A with no lookup in sightDifferent-size ranges in the multiplication form, or an #N/A in the dataMatch the ranges, and fix the error at its source
An OR total is too highA row passed both conditions and was added twiceWrap the added conditions in (...)>0
An OR total is wrongThe added conditions aren't in brackets, so only the last one is multipliedWrite ((cond1)+(cond2))*F2:F11
A January count is too highEmpty rows count as January 1900Add a YEAR test, or stop the range at the last row
The workbook is slowWhole-column references, or thousands of SUMPRODUCT formulasLimit the ranges, use a table, or switch to SUMIFS
Copied formulas give odd resultsThe ranges moved as the formula was copiedLock them: $F$2:$F$11

Frequently asked questions about SUMPRODUCT

What's the difference between SUM and SUMPRODUCT?

SUM adds up values. SUMPRODUCT multiplies the matching items in two or more arrays first, then adds up the products. With a single range, they give the same answer: =SUMPRODUCT(F2:F11) and =SUM(F2:F11) both return $18,700. In Microsoft 365 and Excel 2021 or later, SUM can also work through array calculations, as shown above.

How do I average with conditions using SUMPRODUCT?

Divide a sum with conditions by a count with the same conditions. Here's the average February order:

=SUMPRODUCT((MONTH(A2:A11)=2)*F2:F11) / SUMPRODUCT(--(MONTH(A2:A11)=2))→ $1,945

That's $7,780 from four orders. If no row matches, the count is 0 and you get #DIV/0!. For conditions that AVERAGEIFS can handle, the AVERAGEIF and AVERAGEIFS guide is the simpler route.

How do I count unique values with SUMPRODUCT?

In Excel 2019 and earlier, which don't have UNIQUE, this classic formula counts the different reps:

=SUMPRODUCT(1/COUNTIF(C2:C11, C2:C11))→ 7

COUNTIF counts how often each row's rep appears, so each of Ava's two rows becomes 1/2, and every rep adds up to exactly 1. It returns #DIV/0! if the range has a blank cell. The UNIQUE guide explains it in more detail and shows the Microsoft 365 way.

Can I use SUMPRODUCT as a lookup?

For numbers, yes, and it handles several conditions. What was Sam's monitor revenue?

=SUMPRODUCT((C2:C11="Sam")*(D2:D11="Monitor")*F2:F11)→ $1,500

There are catches, though. If two rows match, SUMPRODUCT adds them together. If none match, you get 0 instead of an error. And it can't return text, such as a rep's name. For a real lookup, use XLOOKUP.

Does SUMPRODUCT ignore hidden or filtered rows?

No. SUMPRODUCT includes every row in its ranges, hidden or not. To total only the rows you can see, filter the table and use =SUBTOTAL(109, F2:F11). SUBTOTAL doesn't take a condition of its own, so the filter does that job.

Which versions of Excel have SUMPRODUCT?

SUMPRODUCT works in every version of Excel, including Microsoft 365, on Windows and Mac, and in Excel for the web, and it doesn't need Ctrl+Shift+Enter. So do the functions this guide uses inside it: MONTH, YEAR, WEEKDAY, EXACT, SEARCH, ISNUMBER and COUNTIF. SUM without Ctrl+Shift+Enter and FILTER need Microsoft 365 or Excel 2021 or later. Google Sheets has SUMPRODUCT too, with the same arguments.

For Microsoft's own examples, see the SUMPRODUCT function page. For more on how array calculations work in Microsoft 365, see dynamic array formulas and spilled array behavior.

For more practice, the conditional math topic has the free sumif-region exercise on the same sales log, plus Premium exercises on two conditions with SUMIFS and COUNTIFS with a number. Two Premium exercises use SUMPRODUCT on a team budget: counting the categories that went over budget and adding up the overspend.

Key takeaways

  • =SUMPRODUCT(array1, [array2], ...) multiplies the arrays item by item and adds up the results. Every array must be the same size.
  • A condition like (D2:D11="Monitor") gives TRUE and FALSE. Multiply it by a range, or put -- in front of it, to turn it into 1s and 0s.
  • Multiply conditions for AND and add them for OR. When a row can match both sides of an OR, wrap the sum in (...)>0 so it's counted once.
  • Use SUMIFS when it can do the job, because it's faster. Use SUMPRODUCT for months, weekdays, case-sensitive matches, OR across columns and columns that need multiplying first.

Practice

Keep going with SUMPRODUCT exercises

Reading gets you started. Solving a few of these is what makes it stick.

  1. 1Revenue for one regionFreeWhat was the total revenue from the North region?→
  2. 2Two conditions at once PremiumWhat was the revenue from Monitors sold in the South region?→
  3. 3COUNTIFS with a number PremiumHow many Laptop orders had 3 or more units?→
  4. 4How many went over? PremiumHow many categories spent more than their budget? Use one formula.→
  5. 5Add up with a condition PremiumWhat is the total overspend: how much the categories that went over budget spent above their budgets, added together?→
All conditional math exercises →
Brian Millot

About the author

Brian Millot

Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.