If you need to **multiply some corresponding components** in a given array and desire to **return the sum** of the resulting products, then follow these quick basic steps using the SUMPRODUCT function in Excel.

**Excel SUMPRODUCT function**

The formula in use here is:

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

There are 2 basic steps to arrive at this result, and they are as follows:

**Step 1:** Prepare your Excel workbook with the array of data to be calculated

**Step 2:** Apply the formula as desired

**Formula for SUMPRODUCT**

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

**Step 1:**

The sample array data is created in the manner as displayed below:

*Figure 1- SUMPRODUCT FUNCTION*

**Step 2:**

At this step, we will go through some series of scenarios where SUMPRODUCT function can be applied to produce results:

**SCENARIO 1**

* Figure 2 – SUMPRODUCT FUNCTION*

In this first scenario, **A2:A5** was used as **array1** and **B2:B5** was used as **array2. **The SUMPRODUCT function is calculated on the **Cell E2**, and the resulting formula is

**=****SUMPRODUCT(****A2:A5****, ****B2:B5****)**

The above SUMPRODUCT function calculates the selected arrays as

and results in **=(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5) ****70**.

**SCENARIO 2**

* Figure 3- SUMPRODUCT FUNCTION*

In this second scenario, **A2:B5** is used as **array1**, and **B2:C5** is used as **array2. **The SUMPRODUCT function is calculated on **Cell G2** and the resulting formula is **=****SUMPRODUCT(****A2:B5****, ****B2:C5****)**

The above SUMPRODUCT function calculates the selected arrays as

and results in **=((A2*B2)+(A3*B3)+(A4*B4)+(A5*B5))+((B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)) ****348**.

**SCENARIO 3**

*Figure 4 – SUMPRODUCT FUNCTION*

In this second scenario, **A2:A5** is used as **array1**, and **B2:B5** is used as **array2. **The SUMPRODUCT function is calculated on **Cell i2** and the resulting formula is

**=****SUMPRODUCT****(--(A2:A5>2),****B2:B5)**

This example has a logical function where array1 checks to see data within the array that does not fulfill the logical condition before any calculation is done.

As it happens in all logical conditions, results are returned in the TRUE or FALSE format. The** “–”** in front of array1 is to change the logical results of **TRUE** or **FALSE** to **1** or **0** respectively. This is because the SUMPRODUCT function recognizes only numeric values.

The above SUMPRODUCT function calculates the selected arrays as explained below:

- Since A2 < 2, 0 is reallocated to A2
- Since A3 = 2, 0 is reallocated to A3
- Since A4 > 2, 1 is reallocated to A4
- Since A5 > 2, 1 is reallocated to A5.

Hence this formula is given as:

**=(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5)**

Which is **= (0*5) + (0*6) + (1*7) + (1*8)**

The result is 15.

**Note**

The SUMPRODUCT function can only work with numeric values, and the selection range size of array1 must be same with selection range size of array2 and on till array n.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

## Leave a Comment