Contents
Standard usage
The basic syntax of our function is simple:
=SUMPRODUCT(Massiv1; Massiv2; … )
The most boring way to use this wonderful function is to use it as described in the help – to sum the pairwise products of cells in two (or more) specified ranges. For example, you can calculate the total cost of an order without an additional column:
In fact, what this function does can be expressed by the formula:
=B2*C2+B3*C3+B4*C4+B5*C5
Technically, there can be not two, but three or more multiplied arrays (ranges) (up to 255). The main thing is that they are the same size. Convenient, but nothing special. However, use SUMPRODUCT the only way is to hammer nails with a microscope, because, in fact, she can do much more.
Working with arrays without Ctrl+Shift+Enter
If you are even a little familiar with array formulas in Excel, you should understand their power and beauty. Sometimes a single array formula can replace multiple columns of extra calculations and manual labor. But array formulas also have disadvantages. The main ones are the relative difficulty of understanding, the slowdown in recalculating the book, and the need to enter these formulas in combination Ctrl + Shift + Enter instead of the usual Enter. And just with the latter, our function can help SUMPRODUCT. The nuance is that it can work with arrays by definition, i.e. does not require pressing Ctrl+Shift+Enter while typing.
This fact is the basis of most of the tricks using SUMPRODUCT (SUMPRODUCT). Let’s, for example, consider a couple of the most typical scenarios.
Counting the number of conditions met
Let’s say we need to count the number of company branches where the plan is fulfilled (that is, the fact is greater than or equal to the plan). This can be done with a single formula with SUMPRODUCT without additional columns:
Multiplication by 1, in this case, is needed to convert the results of comparing the plan and the fact – the logical TRUE and FALSE into 1 and 0, respectively.
Checking multiple conditions
If you need to check more than one condition, then the formula from the previous example will need to be supplemented with one more (or several) factors. And if you need to calculate not the quantity, but the amount, then you can multiply not by 1, but by the range with the summed data:
In fact, it turns out something very similar to the mathematical function of the sample count SUMMESLIMN (SUMIFS), which also knows how to check several conditions (up to 127) and summarize values from a given range based on them.
Logical connectives AND and OR (AND and OR)
If you need to link conditions not with a logical “AND”, as in the example above (Fact>=Plan) И (Region=East), and logical OR, then the design will change slightly – multiplication sign is replaced by a plus:
Calculation based on data from a closed (!) file
In addition to all of the above, SUMPRODUCT There is another non-obvious and very useful property – she knows how to work with data from unopened books. If, for comparison, try to count in another file the number of branches from the region East our book and write something like this:
…then the second formula with the classical function COUNTIFS (COUNTIFS) will only work as long as the source file is open. If you close it, the error #VALUE! Our own function SUMPRODUCT (SUMPRODUCT) calmly counts according to data even from an unopened book!
- What are array formulas and how do they work
- How to perform selective calculations on one or more criteria