In this article:

May 8, 2024

One useful function for calculating total prices is the **SUMPRODUCT **function. Both Excel and Google Sheets have this function. In this tutorial, we will learn how to use **SUMPRODUCT **in both Excel and Google Sheets.

The **SUMPRODUCT **multiplies the numbers along the same row, and then adds the products to arrive at the answer. To make it clearer, we added an illustration below:

We are given two columns A and B with three cells each. What SUMPRODUCT will do is to multiply A1 to B1, A2 to B2, and A3 to B3. Then, SUMPRODUCT will add these products to get the final answer. The answer it gives is A1*B1 + A2*B2 + A3*B3.

The **SUMPRODUCT **function has the following syntax:

**=SUMPRODUCT(Column1,Column2)**

Where **Column1** and **Column2** are the ranges of selected columns 1 and 2. The two columns must have the same length.

In the next examples we will see how it works in both Excel and Google Sheets.

Let us start with a simple example. We want to get the total price of the items bought from your store:

As you can see, you can easily apply the **SUMPRODUCT **function here. Simply specify the two rows as B2:B4 and C2:C4:

**=SUMPRODUCT(B2:B4,C2:C4)**

The result is:

As you can see, **SUMPRODUCT** is a wonderful function that you can use to quickly calculate the total price of the items bought, as long as each column is the same length.

**SUMPRODUCT **can also be used to multiply and sum three or more columns together. The formula will multiply the three numbers along the same row together and then find the sum of the products. For example, we have the following data:

We simply apply **SUMPRODUCT **to these three columns:

**=SUMPRODUCT(A1:A7,B1:B7,C1:C7)**

The result is:

The **SUMPRODUCT **allows you to specify which items to add on the list. For example, we want to add only the **Lettuce **items in the following list:

There is a way to do so. You add a multiplier that gives a value of 1 when it is equal to a certain value and a value of 0 if it is not equal. The multiplier turns out to be a simple function:

**=(A2=$A$13)**

In this example, the cell A13 contains the word or value with which we want to compare the values in Column A. This will give a TRUE or FALSE value:

We will then add this to our SUMPRODUCT function. If we want to add all the prices, we will have the following function:

**=SUMPRODUCT(B2:B11,C2:C11)**

However, since we want to limit those added to the items listed in A13, we now have the following function:

**=SUMPRODUCT((A2:A11=A13)*B2:B11,C2:C11)**

The result is:

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Get your copy of our free Google Sheets automation guide!

- 27 pages of Google Sheets tips and tricks to save time
- Covers pivot tables and other advanced topics
- 100% free

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->