---
title: "SUMPRODUCT function in Excel: syntax and examples"
description: "How to use SUMPRODUCT in Excel and Google Sheets: quantity times price in one formula, and sums with conditions."
canonical_url: "https://excelsavvy.com/functions/sumproduct"
meta:
  "og:description": "How to use SUMPRODUCT in Excel and Google Sheets: quantity times price in one formula, and sums with conditions."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~U1VNUFJPRFVDVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBTVU1QUk9EVUNUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBxdWFudGl0eSB0aW1lcyBwcmljZSBpbiBvbmUgZm9ybXVsYSwgYW5kIHN1bXMgd2l0aCBjb25kaXRpb25zLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~U1VNUFJPRFVDVChhcnJheTEsIFthcnJheTJdLCAuLi4p%2Cp_Ii9mdW5jdGlvbnMvc3VtcHJvZHVjdCI%2Cs_GzObBtyWxxGpZn1B.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "SUMPRODUCT function in Excel: syntax and examples"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~U1VNUFJPRFVDVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBTVU1QUk9EVUNUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBxdWFudGl0eSB0aW1lcyBwcmljZSBpbiBvbmUgZm9ybXVsYSwgYW5kIHN1bXMgd2l0aCBjb25kaXRpb25zLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~U1VNUFJPRFVDVChhcnJheTEsIFthcnJheTJdLCAuLi4p%2Cp_Ii9mdW5jdGlvbnMvc3VtcHJvZHVjdCI%2Cs_GzObBtyWxxGpZn1B.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **The SUMPRODUCT function**

Multiplies ranges row by row and adds the results. With a condition in parentheses it works as a flexible SUMIFS.

=

SUMPRODUCT(

## **How to use SUMPRODUCT**

SUMPRODUCT multiplies lists against each other row by row and then adds up the results. An invoice is the classic case: quantity times price on every line, and the grand total of all lines, in one formula and without a helper column.

In `**=SUMPRODUCT(B2:B10,C2:C10)**` the first range holds the quantities and the second the prices. Every range must have the same size. The function also gives weighted averages: divide by the sum of the weights, as in `**=SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10)**`.

The second use is conditions. A test like `**(A2:A10="North")**` gives TRUE or FALSE for every row, and multiplying by 1 or by another range turns that into numbers: `**=SUMPRODUCT((A2:A10="North")*C2:C10)**`. Several conditions multiplied together work as AND, and added together they work as OR.

For plain conditions SUMIFS is easier to read, and wildcards work there. Reach for SUMPRODUCT when SUMIFS cannot express the rule, such as the month of a date, the length of a text, or OR across columns.

## **Syntax**

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

<dl><dt>**array1 **</dt>
<dd>The first range or array.</dd>
<dt>**array2 **(optional)</dt>
<dd>More ranges of the same size to multiply with.</dd></dl>

## **Examples**

E1`=SUMPRODUCT(<sup>1</sup>B2:B4,<sup>2</sup>C2:C4)`

|  | A | B | C | D | E |
| --- | --- | --- | --- | --- | --- |
| 1 | Item | Qty | Price |  | **450** |
| 2 | Pen | 4 | 10 |  | |
| 3 | Book | 2 | 85 |  | |
| 4 | Bag | 1 | 240 |  | |

**In plain words:** Multiply <sup>1</sup>each quantity in B2:B4 by <sup>2</sup>its price in C2:C4 and add up the results.

E1=SUMPRODUCT((B2:B4>1)\*C2:C4)

|  | A | B | C | D | E |
| --- | --- | --- | --- | --- | --- |
| 1 | Item | Qty | Price |  | **95** |
| 2 | Pen | 4 | 10 |  | |
| 3 | Book | 2 | 85 |  | |
| 4 | Bag | 1 | 240 |  | |

## **Common SUMPRODUCT mistakes**

- SUMPRODUCT returns #VALUE!

  The ranges have different sizes, as in `**=SUMPRODUCT(B2:B10,C2:C9)**`. Every range must cover the same number of rows and columns.
- A condition inside SUMPRODUCT gives 0

  TRUE and FALSE passed as a separate range are treated as zero. Turn them into 1 and 0 first, with a double minus: `**=SUMPRODUCT(--(A2:A10="North"),C2:C10)**`. Multiplying with \* does the same.
- SUMPRODUCT returns #VALUE! when the range includes the header row

  Multiplying with \* fails on a text cell such as a heading. Start the ranges in the row below the header, as in `**=SUMPRODUCT(B2:B10*C2:C10)**`. With commas between the ranges, text is treated as zero.
- A wildcard like "*north*" finds nothing

  Wildcards only work in functions like SUMIFS, not in an = comparison. Search the text instead: `**=SUMPRODUCT(ISNUMBER(SEARCH("north",A2:A10))*C2:C10)**`.

## **Practice SUMPRODUCT**

- [**1****Order total with SUMPRODUCT**→](https://excelsavvy.com/practice/sumproduct/sumproduct-order-total)
- [**2****A weighted average with SUMPRODUCT**→](https://excelsavvy.com/practice/sumproduct/weighted-average)
- [**3****Revenue for one region with SUMPRODUCT**→](https://excelsavvy.com/practice/sumproduct/sumproduct-with-condition)

## **Read the guide**

- [**Excel for finance and accounting: the skills you actually use**](https://excelsavvy.com/learn/excel-for-finance)
- [**Excel inventory data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-inventory-data)
- [**Excel data cleaning practice: a messy file with tasks and answers**](https://excelsavvy.com/learn/excel-data-cleaning-practice)

## **Questions**

<details>

<summary>

### What is the difference between SUMIFS and SUMPRODUCT?

</summary>

SUMIFS adds the cells that meet plain conditions and supports wildcards. SUMPRODUCT multiplies ranges and can test almost anything, such as the month of a date or the length of a text, but it takes a little more typing.

</details>

<details>

<summary>

### How do I use SUMPRODUCT with multiple conditions?

</summary>

Multiply the conditions together and finish with the range to add: =SUMPRODUCT((A2:A6="North")\*(B2:B6="Shoes")\*C2:C6). A row is added only when every condition is true.

</details>

<details>

<summary>

### Do I need Ctrl+Shift+Enter with SUMPRODUCT?

</summary>

No. SUMPRODUCT handles arrays by itself, so a normal Enter is enough, in every version of Excel and in Google Sheets.

</details>

<details>

<summary>

### How do I use OR in SUMPRODUCT?

</summary>

Add the conditions instead of multiplying them: =SUMPRODUCT(((A2:A6="North")+(A2:A6="South"))\*C2:C6) adds the rows that are North or South.

</details>

## **Related functions**

- [**SUM**](https://excelsavvy.com/functions/sum)
- [**SUMIFS**](https://excelsavvy.com/functions/sumifs)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/sumproduct#webpage","dateModified":"2026-11-16","description":"How to use SUMPRODUCT in Excel and Google Sheets: quantity times price in one formula, and sums with conditions.","name":"SUMPRODUCT function in Excel: syntax and examples","url":"https://excelsavvy.com/functions/sumproduct","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/sumproduct#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/3"},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/4"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/sumproduct"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/sumproduct#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"SUMPRODUCT function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/sumproduct#article","dateModified":"2026-11-16","description":"How to use SUMPRODUCT in Excel and Google Sheets: quantity times price in one formula, and sums with conditions.","headline":"SUMPRODUCT function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~U1VNUFJPRFVDVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBTVU1QUk9EVUNUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBxdWFudGl0eSB0aW1lcyBwcmljZSBpbiBvbmUgZm9ybXVsYSwgYW5kIHN1bXMgd2l0aCBjb25kaXRpb25zLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~U1VNUFJPRFVDVChhcnJheTEsIFthcnJheTJdLCAuLi4p%2Cp_Ii9mdW5jdGlvbnMvc3VtcHJvZHVjdCI%2Cs_GzObBtyWxxGpZn1B.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/sumproduct#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/sumproduct#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"What is the difference between SUMIFS and SUMPRODUCT?","acceptedAnswer":{"@type":"Answer","text":"SUMIFS adds the cells that meet plain conditions and supports wildcards. SUMPRODUCT multiplies ranges and can test almost anything, such as the month of a date or the length of a text, but it takes a little more typing."}},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I use SUMPRODUCT with multiple conditions?","acceptedAnswer":{"@type":"Answer","text":"Multiply the conditions together and finish with the range to add: =SUMPRODUCT((A2:A6=\"North\")*(B2:B6=\"Shoes\")*C2:C6). A row is added only when every condition is true."}},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"Do I need Ctrl+Shift+Enter with SUMPRODUCT?","acceptedAnswer":{"@type":"Answer","text":"No. SUMPRODUCT handles arrays by itself, so a normal Enter is enough, in every version of Excel and in Google Sheets."}},{"@id":"https://excelsavvy.com/functions/sumproduct#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"How do I use OR in SUMPRODUCT?","acceptedAnswer":{"@type":"Answer","text":"Add the conditions instead of multiplying them: =SUMPRODUCT(((A2:A6=\"North\")+(A2:A6=\"South\"))*C2:C6) adds the rows that are North or South."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~U1VNUFJPRFVDVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBTVU1QUk9EVUNUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBxdWFudGl0eSB0aW1lcyBwcmljZSBpbiBvbmUgZm9ybXVsYSwgYW5kIHN1bXMgd2l0aCBjb25kaXRpb25zLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~U1VNUFJPRFVDVChhcnJheTEsIFthcnJheTJdLCAuLi4p%2Cp_Ii9mdW5jdGlvbnMvc3VtcHJvZHVjdCI%2Cs_GzObBtyWxxGpZn1B.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~U1VNUFJPRFVDVCBmdW5jdGlvbiBpbiBFeGNlbDogc3ludGF4IGFuZCBleGFtcGxlcw%2Cdescription_~SG93IHRvIHVzZSBTVU1QUk9EVUNUIGluIEV4Y2VsIGFuZCBHb29nbGUgU2hlZXRzOiBxdWFudGl0eSB0aW1lcyBwcmljZSBpbiBvbmUgZm9ybXVsYSwgYW5kIHN1bXMgd2l0aCBjb25kaXRpb25zLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~U1VNUFJPRFVDVChhcnJheTEsIFthcnJheTJdLCAuLi4p%2Cp_Ii9mdW5jdGlvbnMvc3VtcHJvZHVjdCI%2Cs_GzObBtyWxxGpZn1B.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
