SUMPRODUCT function in Excel – application, syntax, examples

Excel is a fairly popular accounting program. And specialists in this field often use the function SUMPRODUCT in his difficult work. It allows them to quickly calculate wages.

As the quick-witted reader can understand, with the help of such a formula, two products can be summed up. The multipliers can be either individual ranges or whole arrays of data. Moreover, this formula can be used as a substitute for an array formula. But let’s talk about everything in order.

SUMPRODUCT – Detailed Description

Despite the apparent simplicity at first glance, this function is not so simple. Just like the range of its application. This is because it performs several actions at once. So, with its help, you can summarize the values ​​based on certain criteria. In addition, it allows you to get rid of the need to use array formulas in your work, which you often have to use for a standard function. SUM.

The methods themselves are:

1. Determining the sum of products of an array.
2. Checking the value for compliance with certain criteria, obtaining their products and summation.
3. Use as a substitute for an array formula.

In fact, possible variations of the methods of using the function SUMPRODUCT much bigger. An experienced Excel user can easily combine several functions in order to make the spreadsheet work the way he needs it. 

Syntax of the SUMPRODUCT function

As arguments for this function, a set of ranges is used, which are first multiplied among themselves, and then the resulting results are summed up. Argument separation is done with a semicolon. It is important to consider that arrays cannot be of different types. In simple terms, you can use either only vertical or only horizontal ranges.

Let’s take the easiest to understand example of using this function. And then we will gradually complicate the task, demonstrating more and more new features of its use. 

So, we have a table in which the values ​​​​of the length and width of different rectangles are known in advance. Our task was to determine the sum of their areas. You can do without the function SUMPRODUCT, by calculating the area by manually multiplying the length by the width, and then sum the resulting values. As a result, such a bulky table will turn out.

But you can greatly simplify the task by using the function SUMPRODUCT, using two arguments: the range of all length values ​​and the range of all width values. After that, the program itself will perform all the necessary actions. 

We see that the result of using the function SUMPRODUCT turned out identical. 

Attention! We have exclusively used length and width values ​​as function arguments. The column with the area for each remained in order to be able to visually compare the functions SUM и SUMPRODUCT.

SUMPRODUCT function with condition

In its purest form, the function SUMPRODUCT used quite rarely. Especially in accounting. It is hard to imagine the situation that it is directly useful every day in order to calculate the sum of the products. However, such situations also happen. For example, if the task is to multiply the exchange rate and the amount in dollars, and then get a general financial indicator based on the cost of each product in national currency. But such a task still happens quite rarely. 

However, this formula is very often used to derive values ​​based on certain conditions.

It is much easier to see this with a real example. Let’s say we have a table of one month’s expenses for a tiny organization. We are faced with the task of determining how much money was spent as a result for the first two months of the year in total for all items of expenditure. 

To accomplish this task with this function, you must use it and write two conditions at the very beginning. Each condition is enclosed in parentheses. Since in our case there must be a match to two criteria at once, we used the asterisk symbol (*). The syntax is visible in this screenshot. You just need to substitute the appropriate values ​​for your case in the required places. And it’s done!

Let’s look at the arguments in more detail:

1. The first condition is written in the first brackets.
2. The second condition is written in the second brackets.
3. The third argument is the array to sum from. 

The final result is 3700 rubles. If you extend this formula to the rest of the lines and replace the conditions in each of them, you can get values ​​that are typical for another reporting period or another item of expenditure. Unfortunately, there is no better way to automate this process. You will have to edit some parameters manually. But such is life. True, if you learn how to write macros, you can achieve absolutely any level of automation when working with spreadsheets. 

Comparison in the SUMPRODUCT function

Comparison is one of the options for using the function SUMPRODUCT. It is carried out in the same way as if based on the criterion. It is much easier to demonstrate how the comparison works in the function SUMPRODUCT with a specific example. Let’s say we have complicated the task. Now we do not need to determine all expenses, but only small ones. Suppose these are those costs whose amount is less than 1000 rubles. 

We use the same arguments in the function, but we add a comparison operator to them. That is, in our case – D: D<=1000. As a result of the calculations, the number 1000 was obtained.

What is this indicator? And this is the thousand that was used to buy pencils at the beginning of the year. And after we added an additional criterion, the answer was as follows.

Now let’s again extend the formula to all the remaining cells and partially change the information. After that, we will see the total amount of funds spent for each item of expenditure. 

Standard usage of SUMPRODUCT

Of course, there is nothing more boring and banal than the standard use of the function SUMPRODUCT. And all this is very easy, and understandable immediately. However, let’s give another example and a universal formula for this function.

=SUMPRODUCT(Array1, Array2, … )

That is, the function adds two numbers from each array in pairs, and then multiplies them together. The advantage of this function is that you can do without additional columns, as described in the example above. All necessary operations are performed automatically. Everything can be expressed with just one simple formula.

The number of ranges that can be used is limited to 255. But this is more than enough. The main requirement is that they must be the same size.

In fact, the function is similar to such an expression.

=B2*C2+B3*C3+B4*C4+B5*C5

Logical connectives AND and OR (AND and OR)

In the example above, it was described how you can make a logical connection И. But what if we need the described cells to match at least one of the criteria? In such a case, you must use the plus sign, as shown in this screenshot.

Finding the sum of products of array elements

Now let’s take a closer look at how you can use the function to find the sum of the products of array elements. 

The syntax is the same, because in fact this formula works with data arrays. That is why it has great prospects in different fields of activity at once.

This function performs element-wise operations with all ranges (even if there are 200 of them) that are part of the array. 

A mandatory requirement for arrays is the presence of the same dimensions. If the arrays contain a different number of elements, a #VALUE! error will be returned.

In general, this formula is not intended to be used with non-numeric values. In such cases, they will be read as zero. But in some cases it is possible to work with text values. As a rule, this is when it comes to compliance with certain conditions. Let’s explore this topic in more detail.

Summing and counting values ​​that meet the criteria

If we write such a formula, the result will be zero: =SUMPRODUCT(A3:A6>2). If you go to the formula bar, and there select the part that is written in brackets, and then press F9, an array will be obtained: TRUTH: TRUTH и TRUE: FALSE. 

If we get such a result, then everything is fine.

One caveat must be taken into account here: if the value TRUE equals one, and FALSE – zero, this does not mean that any mathematical operations can be performed with these numbers. To do this, you need to use two minus signs that are nearby. In this case, the array will be converted to a numeric form, and you can perform mathematical operations with it. 

The question is, how is it possible to use criteria in relation to text values. Here is a simple formula, after which everything immediately becomes clear.

=SUMPRODUCT(—(B3:B6=”apples”))

That is, we wrap the word in quotation marks, after which we simply equate the desired cells to this text value. Similarly, you can use logical operators to test a range against multiple criteria. 

Checking multiple conditions

To check for compliance with several conditions at once, it is necessary to multiply the criteria among themselves or add them (use the + sign). That is, apply the logical operators described above. 

SUMPRODUCT as an array formula

Function SUMPRODUCT can also be used as an array formula. When should it be done? First of all, when it is necessary to perform operations with a large number of values. In this case, you can not use the array formula SUM, but simply replace it with the function SUMPRODUCT. There are several examples of how this can be used in practice.

=SUMPRODUCT(—ISNULL(D2:D23)) – in this case, empty cells are searched for in the range, which are then counted. 

=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) – here the selection of values ​​​​that are greater than the arithmetic mean is carried out, after which their sum is calculated. 

Calculation based on data from a closed file

And now the main feature. Once you know about it, you will definitely fall in love with this feature. The fact is that she knows how to find information in those files that are not currently open. As you know, many other functions are able to search only in those books that were previously opened on the computer. In this case, the user may not even open them. This is especially convenient when there are few computer resources (for example, the table is large, many parallel necessary tabs are open in the browser, and there is not enough RAM on the computer). In this case, this feature will come in handy. 

Here is a screenshot where we get data about the eastern branch of the company. 

How does it work in other formulas (or rather, does not work)? If the document from which the information is being scooped is not currently open, the #VALUE! In the case of using the function SUMPRODUCT, if you try to read data from a file, you can do it. This opens up a lot of possibilities, because it allows you to use inexpensive computers to perform complex calculations or comparisons.

So the function SUMPRODUCT makes it possible to use a whole range of spreadsheet capabilities, from the functions for which it was created to non-standard uses. As you can see, there is nothing complicated. It is much easier to use than arrays of functions (although there is nothing complicated in that topic). However, the function SUMPRODUCT makes it possible to completely avoid the need to use an array formula for calculations of this kind. And this is good news.