Excel has many functions that can help with your work. And there will be different functions to achieve different purpose. Therefore, today we will launch the usage of SUMPRODUCT function to sum up values based on certain conditions.
Sometimes you may get a mess table. But you have to calculate certain numbers. For example, in the image below, there are sales volumes of different people.
And it includes many products and different months. If you want to calculate the volume of apple that William has sold, you need first search the target number. Of course this process is very cumbersome. So you can use the SUMPRODUCT function. And now let’s first see the usage of this function.
The Usage of SUMPRODUCT Function
Actually, the rule of this function is very simple. It will multiple the given numbers and then gives the sum of those numbers. In the picture below, there are two arrays.
And input the formula into a blank cell:
=SUMPRODUCT(A1:A3,B1:B3)
And then press the key “Enter”. Thus, the function will give you a number “32”. Because the calculating process is: the result=1*4+2*5+3*6=32.
Besides, the arrays can also expand. And the following is another example, there are two larger arrays.
And now input this formula:
=SUMPRODUCT(A1:B3,D1:E3)
Then press the key “Enter” and you can see the result “217”. And in the actual usage, you can refer to the following part
Calculating Sales Volume under Certain Conditions
The following are the steps of using SUNPRODUCT function to calculate the volume of apple that William has sold.
- Click a blank cell where you want to input the result.
- Input this formula into the cell:
=SUMPRODUCT(–(A2:A24=”William”),–(C2:C24=”apple”),D2:D24)
In this formula, the scope is “William” in column A and “apple” in volume C. Thus, the formula will automatically add the prices that satisfy both of the two conditions.
- Press the key “Enter”. And then you will see the apple sales volume of William. The result is exactly the sum of the two red numbers in the image below.
You can also change the condition and get the sales volume of other people or other products. In addition, instead of find the number one by one, this function is more convenient.
Method to Deal with Damaged Excel
If your computer is assault by virus or malware, your Excel will also have the tendency of corruption. You don’t know when this data disaster will happen, but you can use a tool to repair Excel. Thus, you can get back almost all your essential information.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com
Leave a Reply