On this page ⌄
- The SUMPRODUCT syntax
- A basic SUMPRODUCT example: units times price
- Calculate a weighted average with SUMPRODUCT
- How SUMPRODUCT handles conditions
- What the double minus (--) does
- SUMPRODUCT with multiple criteria (AND)
- SUMPRODUCT with OR conditions
- Avoid double counting with OR
- Count rows with conditions
- Sum by month, year or weekday
- Case-sensitive sums and counts with EXACT
- Partial matches: SEARCH instead of wildcards
- Multiply and filter in one step
- SUMPRODUCT vs SUMIFS: which to use
- Fix #VALUE! and other SUMPRODUCT errors
- SUMPRODUCT in Microsoft 365: SUM works too
- Common mistakes
- Frequently asked questions about SUMPRODUCT
- Which versions of Excel have SUMPRODUCT?
- 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,500The 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: Date | B: Region | C: Rep | D: Product | E: Units | F: Revenue | G: Price | |
|---|---|---|---|---|---|---|---|
| 2 | Jan 4, 2026 | North | Ava | Laptop | 3 | $3,600 | $1,200 |
| 3 | Jan 9, 2026 | South | Leo | Monitor | 5 | $1,250 | $250 |
| 4 | Jan 15, 2026 | North | Maya | Laptop | 2 | $2,400 | $1,200 |
| 5 | Jan 22, 2026 | East | Sam | Keyboard | 12 | $720 | $60 |
| 6 | Feb 3, 2026 | West | Zoe | Monitor | 4 | $1,000 | $250 |
| 7 | Feb 11, 2026 | North | Ava | Keyboard | 8 | $480 | $60 |
| 8 | Feb 19, 2026 | South | Ivy | Laptop | 4 | $4,800 | $1,200 |
| 9 | Feb 27, 2026 | East | Sam | Monitor | 6 | $1,500 | $250 |
| 10 | Mar 6, 2026 | West | Noah | Laptop | 1 | $1,200 | $1,200 |
| 11 | Mar 14, 2026 | North | Maya | Monitor | 7 | $1,750 | $250 |
The SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], ...)| Argument | What it means | Example |
|---|---|---|
| array1 | The first range or array. On its own, SUMPRODUCT just adds it up | E2:E11 |
| array2, array3, ... | Optional. More ranges or arrays the same size as array1, up to 255 in all | G2: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,700SUMPRODUCT 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,700Column 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,700Both 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)→ $592AVERAGE 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.62That'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.7Emma 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,500Here's what happens, row by row:
| Row | D: Product | D2:D11="Monitor" | × F: Revenue | = |
|---|---|---|---|---|
| 2 | Laptop | FALSE | $3,600 | 0 |
| 3 | Monitor | TRUE | $1,250 | 1,250 |
| 4 | Laptop | FALSE | $2,400 | 0 |
| 5 | Keyboard | FALSE | $720 | 0 |
| 6 | Monitor | TRUE | $1,000 | 1,000 |
| 7 | Keyboard | FALSE | $480 | 0 |
| 8 | Laptop | FALSE | $4,800 | 0 |
| 9 | Monitor | TRUE | $1,500 | 1,500 |
| 10 | Laptop | FALSE | $1,200 | 0 |
| 11 | Monitor | TRUE | $1,750 | 1,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)→ 0The 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,500The 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.
What was the total revenue from the North region?
Write your formula in the green cell, I2.
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,000Two 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,000Two 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,150Five 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,700That'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,200That'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"))→ 4With 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))→ 3That'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))→ 5Without 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,780MONTH 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,780The 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,350January 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,500On 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,200That'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,000Each 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
| SUMIFS | SUMPRODUCT | |
|---|---|---|
| AND conditions | Yes | Yes, multiply them |
| OR conditions | Add several SUMIFS, which can double count across columns | Add the conditions, with >0 across columns |
| Functions on the range, such as MONTH, YEAR or TRIM | No | Yes |
| Multiply columns before adding | No | Yes |
| Wildcards | Yes | No, use ISNUMBER and SEARCH |
| Case-sensitive matches | No | Yes, with EXACT or FIND |
| Text in the range to add up | Skipped | Zero in the comma form, #VALUE! when multiplied |
Whole columns like F:F | Calculated efficiently | Every cell is calculated, so avoid them |
| Speed on large data | Much faster | Slower |
| 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 ranges | Comma form, such as =SUMPRODUCT(--(D2:D11="Monitor"), F2:F11) | Multiplication form, such as =SUMPRODUCT((D2:D11="Monitor")*F2:F11) |
|---|---|---|
| Text, such as TBC | Counted as 0 | #VALUE! |
| A number stored as text | Counted as 0 | Converted to a number |
| An empty cell | Counted as 0 | Counted as 0 |
| The header row | Counted as 0 | #VALUE! |
| An error, such as #N/A | The same error | The 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/AEither 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,500The 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,050If 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,780When 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
| Problem | What's going on | Fix |
|---|---|---|
| Result is 0 | A condition stands alone in the comma form, so TRUE and FALSE count as zero | Put -- in front of it, or multiply it by the range |
Result is 0 with * or ? | = doesn't understand wildcards | Use ISNUMBER(SEARCH("text", D2:D11)) |
| Result is 0 or too low | Extra spaces in the data, or numbers stored as text in the comma form | Clean 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 header | Start and end every range on the same rows, or use the comma form |
#VALUE! from a condition | The comparison isn't in brackets, so Excel multiplies "Monitor"*F2:F11 first | Write (D2:D11="Monitor")*F2:F11 |
#N/A with no lookup in sight | Different-size ranges in the multiplication form, or an #N/A in the data | Match the ranges, and fix the error at its source |
| An OR total is too high | A row passed both conditions and was added twice | Wrap the added conditions in (...)>0 |
| An OR total is wrong | The added conditions aren't in brackets, so only the last one is multiplied | Write ((cond1)+(cond2))*F2:F11 |
| A January count is too high | Empty rows count as January 1900 | Add a YEAR test, or stop the range at the last row |
| The workbook is slow | Whole-column references, or thousands of SUMPRODUCT formulas | Limit the ranges, use a table, or switch to SUMIFS |
| Copied formulas give odd results | The ranges moved as the formula was copied | Lock 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,945That'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))→ 7COUNTIF 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,500There 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
(...)>0so 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.
- 1Revenue for one regionFreeWhat was the total revenue from the North region?→
- 2Two conditions at once PremiumWhat was the revenue from Monitors sold in the South region?→
- 3COUNTIFS with a number PremiumHow many Laptop orders had 3 or more units?→
- 4How many went over? PremiumHow many categories spent more than their budget? Use one formula.→
- 5Add up with a condition PremiumWhat is the total overspend: how much the categories that went over budget spent above their budgets, added together?→

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.