How to Use Excel SUMPRODUCT Function in Office 365?
Tags: excelfunctions, excelsumproductfunct
Published: 2020-08-27 11:02:25

---
title: How to Use Excel SUMPRODUCT Function in Office 365?
published: true
date: 2020-08-27 09:20:59 UTC
tags: ExcelFunctions,ExcelSUMPRODUCTFunct
canonical_url: https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/
---

## Excel SUMPRODUCT Function:

![Excel SUMPRODUCT Function](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/Excel-SUMPRODUCT-Function.png)<figcaption id="caption-attachment-12697">Excel SUMPRODUCT Function</figcaption>

In this tutorial, we will discuss the explanation, syntax, and usage of the **Excel SUMPRODUCT Function in Office 365**. Get an official version of **Microsoft Excel** from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel).

## Explanation of SUMPRODUCT Function:

- The SUMPRODUCT function **multiplies the corresponding arrays or ranges and returns the sum of the products**.
- In a simple sentence, the values within the arrays are multiplied and the products are added and returned as output.
- The default operation of this function is multiplication, however, it can perform addition, subtraction, and division.

## SUMPRODUCT Function – Basic Syntax:

The fundamental syntax of **SUMPRODUCT Function** is,

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

### Argument Description:

- **Array1 (Required):** The ranges of arrays/cells that you want to multiply and then add.
- **[array2], [array3],…(Optional):** Array arguments **2 to 255** whose values you wish to multiply and then sum. You can give up to **30 arrays**.

### Remarks:

- The function will **simply sum the values** in the array **if only one array is supplied**.
- Although this function deals with arrays, you don’t have to follow the normal array syntax. That means you don’t have to press _Ctrl + Shift + Enter_ after entering the ranges. Like a non-array formula, pressing _ **‘Enter’** _ would be enough.
- **To do other arithmetic operations,**
  - Use the arithmetic operators  **(\*, /, +, -) **by replacing the commas that are used to separate the array arguments. For example,
  - As usual, after all the operations are done, the products will be summed and returned as output.
  - **Enclose your array arguments** within the **parentheses** while using arithmetic operators so it controls the order of operators.

### Important Note:

- **Non-numeric entries will be treated as zeros. **
- **#VALUE! Error** – **Array arguments**  **must have the**  **same size/ranges** or else the function returns the #VALUE! Error. For example, =SUMPRODUCT(A1:A5, C1:C6) will return an error.
- Don’t use full column references, for example, =SUMPRODUCT(A:A, C:C) as it will multiply 1,048,576 cells in column A with the 1,048,576 cells in column C and then add.

## Practical Examples:

Let’s look at some practical examples of **SUMPRODUCT Function**  and explore how to use it in Microsoft Excel.

**Example 1: Two arrays are multiplied and the products are added**

- In this example, we are going to multiply the **Quantity** and **Price** of the products, and the output will return as a sum.
- First, we have to select the 1st array range from **E4:E6** and the 2nd array range from **F4:F6**.
- Then, refer to the below syntax of the **SUMPRODUCT**  **function** to return the output.

```
=SUMPRODUCT(E4:E6,F4:F6)
```

![Example 1](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/Example-1-1.png)<figcaption id="caption-attachment-12696">Example 1</figcaption>

- The SUMPRODUCT calculates these arrays as follows:

```
=(10\*10000) + (22\*1500) + (57\*450)
```

- Now, you have to press **Enter** so the output will return as the product’s sum in the **F8 cell**.

![Example 1 - Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/Example-1-Output-1.png)<figcaption id="caption-attachment-12695">Example 1 – Output</figcaption>

**Example 2: Non-numeric Entries treated as Zeros**

- If there are non-numeric entries within the array range, this function will consider it as zeros.

![Non-numeric entries treated as Zeros](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/Non-numeric-entries-treated-as-Zeros.png)<figcaption id="caption-attachment-12704">Non-numeric entries treated as Zeros</figcaption>

**Example 3: #VALUE! Error**

- The function returns the #VALUE! Error if the array arguments are not in the same size or range.

![#Value! Error](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/08/Value-Error.png)<figcaption id="caption-attachment-12705">#Value! Error</figcaption>

## A Brief Summary:

In this short tutorial, we have illustrated how to use **EXCEL**  **SUMPRODUCT Function** in **Office 365** with clear-cut practical examples. If you have any **queries/doubts** , kindly share it in the below comment section. Thanks for visiting **[Geek Excel!!](https://geekexcel.com/)** Keep Learning With Us!!

## Read Ahead:

- [How to Use “Excel RAND Function” in Office 365? – {with Examples}](https://geekexcel.com/how-to-use-excel-rand-function-in-office-365-with-examples/)
- [Steps To Use Excel ISEVEN Function in Office 365 {Simple Way}!!](https://geekexcel.com/steps-to-use-iseven-function-in-microsoft-excel-365-simple-way/)
- [How to Use Excel PHONETIC Function in Office 365?](https://geekexcel.com/how-to-use-phonetic-function-in-microsoft-excel-365/)