Shulou(Shulou.com)11/24 Report--

Original title: "learn this function, one at the top of four!" "

The most difficult thing in Excel should be the function formula.

For example:

The above formula, SUM function is very simple, I believe we all know it is summation.

As a result, it can be calculated by word of mouth, which is 10.

What does this function mean?

Some of the students can't answer right now.

In fact, it is very simple. SUM is added and PRODUCT is multiplied.

So the result of the calculation is to multiply all the numbers: 24.

You must be dissatisfied: I just don't know. If I knew, I would have answered it right away.

It doesn't matter, make it a little more difficult:

Now that you have learned the SUM and PRODUCT functions, what should the result be if you combine these two functions?

A: the result is 10.

Why?

In this lesson, let's learn the simple and complex function SUMPRODUCT!

The basic use of SUMPRODUCT is to multiply two columns of data and then add them.

For example, the result of the above calculation is that two columns of each cell are multiplied and then added, and the result is 40.

So this effect is usually used to calculate the addition of two columns that need to be multiplied.

For example, sales volume and product unit price:

To calculate the total sales, a SUMPRODUCT formula is done:

Is it super easy to have a master to lead the way? SUMPRODUCT (E2 E10)

The parameters of SUMPRODUCT are as follows:

-Parameter 1, the first column of data to multiply

-Parameter 2, the second column of data to multiply

-Parameter 3, and so on. You can select up to 255 columns of data.

Welfare Tips send two more welfare tips:

The multiplication effect of the ▋ parameter is the same. The result of the following two formulas is the same, essentially multiplying two columns of data:

When there is an error value, continue to see if you can find it in the R & D department.

The principle of = SUMPRODUCT (E2E2:E10*F2:F10 E10) is the same as the summation within the SUM function below.

= SUM (1pm 2pm 3) = SUM (1pm 2pm 3) ▋ single column data is added if there is only one column of parameters, then the effect is the same as using the SUM function.

So, you should understand why the first case is 10, because there is only one column of data.

▋ multiplies the number to give another case.

Now the boss thinks that the unit price of the product is too low, and the unit price of each product should be increased by 5 yuan, so how should the total sales be calculated?

You don't have to add 5 to each unit price, just get it done with a SUMPRODUCT formula.

= SUMPRODUCT (E2 E10rect F2 F10room5) see? It's very simple, just add 5 to the summation column.

This calculation process actually adds 5 to each cell in this area:

So, adding a 5 is essentially the same as adding a column of 5.

Up 5 yuan or not high enough, the direct price doubled by 2, how should be calculated?

The formula goes like this:

The principle of his calculation is the same as the plus sign, as follows

After all, ▋ conditional counting is a combination of SUM and PRODUCT functions, and SUMPRODUCT is used more than that.

Look at the following question. If you want to count the number of people in each department, the formula is as follows:

= SUMPRODUCT ((B2:B10=G2) * 1) Analysis of ideas:

B2:B10=G2, in fact, just look at B2, B3,. , B10 cell, is it the same as G2, that is, "sell one"?

If the same, it is true, which is 1, and the result is 1

If it is not the same, it is false, which is 0, and the result is 0.

Finally, it adds up to the number that meets the criteria.

If there are more than one condition, multiply it by the logical value judged by the condition. For example, to judge the number of girls in each department:

The formula is as follows:

= SUMPRODUCT ((B2:B10=G2) * (D2 D10 = "female")) and, for example, to count the number of girls in each department before 2010, multiplied by a conditional value:

The formula goes like this:

= SUMPRODUCT (B2:B10=G2) * D2YEAR D10 = "female") * YEAR (E2:E102010) usage have you summed up?

In a word, multiply by as many times as there are conditions:

Is ▋ conditional summation nothing more than that? SUMPRODUCT must not be convinced.

Now we are going to count the sales of each department.

The corresponding SUMPRODUCT formula looks like this:

= SUMPRODUCT ($Elu2VRO ($B$2:$B$10=H2)) the calculation process is as follows:

If there are multiple conditions, such as calculating the sales of "product C" for each department:

The formula is as follows:

= SUMPRODUCT (($E$2:$E$10) * ($F$2:$F$10) * ($B$2:$B$10=H2) * (D2F$2:$F$10 D10 = "product C")) is the same as the previous counting principle, just multiply the data column and the condition column.

You may be a little confused when you write it here at the end:

These calculations can be realized with SUMIFS and COUNTIFS, so why use SUMPRODUCT?

Isn't the reason obvious? One function is worth four!

You know, with a different number of conditions, you may have to switch back and forth between SUMIF, SUMIFS, COUNTIF, and COUNTIFS.

SUMPRODUCT, on the other hand, uses only one function.

In addition, SUMPRODUCT uses logic operators to directly compare and judge, which makes the logic of the formula clearer.

More importantly, it can also calculate more complex condition judgments.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony, editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.