The omnipotent SUMPRODUCT for learning Excel functions!

In the previous article "The magical AGGREGATE of Excel function learning, one can actually be worth 19! 》, we learned about a powerful statistical function. Today we are going to talk about the SUMPRODUCT function. The sumproduct function can get the sum of the products of two columns of data. However, there are many things to pay attention to when using the function, especially after using logical values ​​and arrays. Let's do it together. Check out the explanation!

1. The essence of SUMPRODUCT

The SUMPRODUCT function is really very simple. To get the sum of the products of two columns of data, we use a simple example to illustrate the basic function of the function:

The picture above is a very simple table to calculate the total price Generally, the unit price * quantity is calculated and then summed. The result is shown in D8. If you use the SUMPRODUCT function, you can directly calculate the total price using the unit price and quantity. Formula 1 is: =SUMPRODUCT(B2:B7,C2:C7), and the result is shown in D9.

In this formula, two parameters are used, namely the unit price area (B2:B7) and the quantity area (C2:C7). The function of the function is to convert the first The data in the first parameter (unit price) and the second parameter (quantity) are multiplied and then summed.

2. Reasons for most errors

Many friends often get error values ​​when using this function, mostly because The area size selection is inconsistent. For example, in the following case, the first parameter has 7 cells and the second parameter only has 6 cells:

Use The SUMPRODUCT function must ensure that the area size of each parameter is the same, but many friends have not noticed this.

3. Another common way of writing is to change the comma into a multiplication sign (*)

For this example, there is another way of writing that is more For common use, the formula is as follows: =SUMPRODUCT(B2:B7*C2:C7)

You can see that the calculation result is the same as =SUMPRODUCT(B2:B7,C2: C7) is consistent.

The consistent results have led to a question that many friends are puzzled by: What is the difference between the two?

4. The difference between a comma and a multiplication sign (*)

Although it only changes the comma in the first formula into a multiplication sign (*), but the meaning of the formula changes. The first formula (SUMPRODUCT(B2:B7,C2:C7)) has two parameters, while the second formula (B2:B7*C2:C7) has one parameter. (To determine how many parameters there are, you need to see whether they are separated by commas.) In the first formula, the step of multiplying two areas is completed by a function. The function does two things. First, let the two areas The corresponding data are multiplied, and then the products are added. In the second formula, the multiplication of two areas is done by array calculation, and the function only does one thing, which is to add the product values.

What is the impact of the change in meaning?

Let’s look at it through an example:

In the formula above, a comma (,) is used, and there are two independent parameters. The SUMPRODUCT function first multiplies the two sets of data correspondingly. During the multiplication, it checks the data and treats non-numeric data as 0, and then adds the products. Therefore, B1 "Unit Price" and C1 "Quantity" will be treated as 0, and the formula can obtain correct results.

When we replace the comma with an *, the formula result is wrong. why? The SUMPRODUCT function is only responsible for adding the products at this time. Parameters B2:B7*C2:C7 are array multiplication operations. Because the calculation area contains text (text cannot be multiplied), there is an error value in the calculation result of this array. Select "B2:B7*C2:C7" in the formula and press F9 to view the operation results of B2:B7*C2:C7:

You can see the first ( The result of the operation (unit price * quantity) is the error value. Next, SUMPRODUCT sums the data containing error values, and the result must be an error.

5. Key points of using the multiplication sign (*) in SUMPRODUCT

The above content has two meanings:

First, using commas and using * sometimes have the same results, but the meanings are completely different. I hope everyone can understand.

Second, when using the multiplication sign (*) in the SUMPRODUCT function, you must pay attention to two points: first, there cannot be uncalculated content, such as text; second, if two or more sets of arrays are multiplied, , the data area size is the same. When using commas, you only need to ensure that the size of the data area is consistent.

6. In fact, the multiplication sign also brings greater advantages

We use A, B is replaced by SUMPRODUCT (A, B) and SUMPRODUCT (A*B). When it is a comma, A and B must both be numeric values ​​or arrays at the same time. One cannot be a numeric value and the other is an array. When it is a multiplication sign, A and B can be both numeric values ​​or arrays at the same time, or one can be a numeric value and the other an array. .

That is:

Therefore, the application of the SUMPRODUCT function is expanded with the multiplication sign. What you are about to see below are applications of the SUMPRODUCT function using the multiplication sign.

7. Do you understand these SUMPRODUCT formulas?

If you understand the above content, it means that the basic usage of this function is no problem. But it is very likely that you still can’t understand many SUMPRODUCT formulas, such as this one:

This is actually a conditional counting problem, look at this one again:

This is a multi-condition summation problem, and there is this:

In this table, almost all kinds of statistical problems are You can use the SUMPRODUCT function to solve it, so I won’t give examples one by one. There may not be many friends who can understand just the three formulas listed above.

Why do I understand how to use the SUMPRODUCT function but still can’t understand these formulas, let alone use them to solve problems myself? The reason is that you don't understand these two knowledge points: logical values ​​and arrays.

8. Learn a little about logical values ​​and arrays

Let’s talk about logical values ​​first. There are only two logical values, TRUE and FALSE. When we make some kind of comparison or judgment in a formula, a logical value will be generated. Take SUMPRODUCT((I2:I22="Zhang San")*1) as an example, where (I2:I22="Zhang San") ) is a judgment. Normally, we make judgments based on cells. I2 = "Zhang San" means to judge whether the content of cell I2 is "Zhang San". If so, it will get TRUE, otherwise it will get FALSE. When we use an area to judge, we will get a set of data, which is actually an array. For example, I2:I22="Zhang San" will get a set of logical values. You can use F9 to see the calculation results:

If you are interested, you can compare the salespersons one by one. And the result value, you will find that Zhang San’s corresponding values ​​are all TRUE.

Because logical values ​​cannot be summed directly, they must be converted into numbers. The conversion method is to perform any operation such as addition, subtraction, multiplication, or division on the logical values. In this formula, *1 has this effect. You can see the effect:

After the *1 operation, TRUE becomes 1, and FALSE becomes 0. Don't ask me why, this is how Excel stipulates it, and this is the corresponding relationship between logical values ​​and numbers.

Okay, we first get a set of logical values, then use *1 to turn them into a set of numbers, and then sum them up to achieve the purpose of counting by condition. Do you understand the formula =SUMPRODUCT((I2:I22="Zhang San")*1) now?

Now we understand logical values ​​and the first principle of array operations: when a group of numbers is calculated with a number, each data in the group of numbers is calculated with this number. calculate. This is how it is calculated in the formula just now.

9. No matter how complicated the SUMPRODUCT formula is, understand it all!

Let’s take a look at the second formula: =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="Second Branch")*( G2:G22="shirt")*J2:J22).

Don’t look at the length of this formula. It is actually very simple to use the knowledge we just learned to crack it. The formula still has only one parameter, but this parameter is composed of four arrays, three of which are logical values, namely (MONTH(F2:F22)=3), (H2:H22="Second Branch") and (G2:G22="shirt"). These three sets of logical values ​​complete three judgments and correspond to three conditions: month = 3, store = second branch and name = shirt. The specific content is as shown in the picture:

It looks densely packed, but after multiplication, it becomes a bunch of 1s and 0s. The result is like this:

The product result has only two 1s, which actually corresponds to the two pieces of data about the shirts from the second branch in March. The formula at this time becomes a set of logical values ​​(already 0 and 1) multiplied by a set of data (quantity), and then the function completes the sum.

In this example, you need to pay attention to the second principle of array calculation: when multiple (including two) arrays are calculated (four arrays in this example), the data at the corresponding position in the array is calculated. It is required that the number of data contained in the array is the same.

So far we have understood the two principles of array calculation. Here is a simple example to illustrate:

An array (A1:A9) and a data When (B1) is multiplied, this group of numbers is multiplied by this data respectively;

When two arrays (A1:A9 and B1:B9) are multiplied, it is Multiply the data in the corresponding positions of the first group of numbers and the second group of numbers.

Now look back at this formula=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="Second Branch")*(G2:G22="Shirt")* J2:J22), it should be less of a headache.

In fact, the calculation principles of arrays are more complicated. Friends who are interested can check out the relevant information. You can learn the simple principles here. Let’s take a look at the third formula: =SUMPRODUCT((H2:H22=H2)*(G2:G22={"sweater","shirt"})*L2:L22). This formula looks a little different from the previous two, (G2:G22={"sweater","shirt"})This part uses a constant array. In fact, this formula should be written like this:=SUMPRODUCT((H2:H22=H2)*(G2:G22="Sweater")*L2:L22) SUMPRODUCT((H2:H22=H2)*(G2:G22="Shirt")*L2:L22 ).

This formula uses two SUMPRODUCT functions. The first is to calculate the sales of sweaters in the main store, and the second is to calculate the sales of shirts in the main store. See respectively. Everyone should be able to understand. The two SUMPRODUCT functions are exactly the same except for "sweater" and "shirt". In this case, you can use a constant array to put the two contents together to make the formula simpler.

Through today's explanation, we have understood the common SUMPRODUCT formula, solved the confusion about using the multiplication sign of the SUMPRODUCT function, and mastered more applications of the SUMPRODUCT function. We also learned some basics about logical values ​​and arrays - which will help us understand other complex formulas.

Related learning recommendations: excel tutorial

The above is the detailed content of The omnipotent SUMPRODUCT for learning Excel functions!. For more information, please follow other related articles on the PHP Chinese website!