Using function sumproduct in webgiaidap.com with many advanced conditions and 11 good examples (to count, to sum, …)
It’s been more than a billion years since Truong wrote articles, but once he did, he had to have quality and depth. The proof is that a hot topic like this was completed in 2 days of hard work.
You are viewing: What is Sumproduct
Main contents:
– Basic understanding of functions
– Examples of array comparisons
– Conditional summation
– Count cells that satisfy multiple conditions
– And many other great content
1. What is the sumproduct function and the syntax in webgiaidap.com
1.1. Define:
The sumproduct function is basically a webgiaidap.com function that allows to multiply numbers in specific arrays (area) and then add the results of these products to get a final result.
It can be understood simply as follows:
Sumproduct = Sum ( Product )
Product: TichSum: Total
Sumproduct is the Sum of Products
Note:
In fact, here is a function of Difficulty on both levels:
– Confusing to write functions
– Difficult to apply to calculate in webgiaidap.com
But this function is extremely effective in many cases when you know how to use it.
1.2. Function syntax
= Sumproduct(Array1, Array2, Array3, …)
Array is array.
Important Note about Arrays/Arrays:
– You can use up to 255 arrays in webgiaidap.com 2007 onwards, and 30 arrays for webgiaidap.com 2003
– The size of the arrays must be the same, otherwise Sumproduct will return the #VALUE . error result
– Any data contained in the arrays is not numeric data, the function will return the result 0
2. How to use the advanced sumproduct function/multiple conditions.
2.1. Example of basic usage of the Sum of Product . function
As shown in the image above, if you do not know how to use the vlookup function, you can calculate the total revenue easily:
– Add the Revenue column
– Calculate revenue for each sale
– Calculate the total sales at the bottom of the sales list.
But doing so would be a bit laborious.
Solution:
Use the sumproduct function to calculate the sum of the products (product here is quantity * selling price)
The function syntax in this case is as follows:
= Sumproduct(A2:A5, B2:B5) = Sum(A2*B2, A3*B3, A4*B4, A5*B5) = 135
In there:
A2:A5: Quantity B2:B5: Selling price
2.2. How to use the advanced sumproduct function/multiple conditions
a/ Use sumproduct function to count with 1 condition
In the sales list in the image above, we have 2 indicators: the expected sales volume and the actual sales volume.
Our task is to count how many sales transactions have actual sales less than expected.
If you do not know how to use the sumproduct function, you will have to add an extra column and then use countif to count, which is a bit laborious and will be difficult to do when the list has thousands of transactions.
See also: What is the U Nong ward – Meaning of the word “Ngu Binh U Nong” in Vietnamese
As for the sumproduct function, it’s too simple as follows:
= Sumproduct ( — (Expected Sale Area > Actual Sale Range ) = Sumproduct (–(B2:B10>C2:C10)) = 4
In just a blink of an eye.
b/ Sumproduct function with multiple conditions used to count
Continuation of part a of the advanced application sumproduct function. We add 1 more condition when counting that is:
Count the number of transactions of item A that meets the condition Actual Sales
It’s actually a lot more difficult,
We use the function as follows:
= Sumproduct ((A2:A10 = “A”)*(B2:B10Click to download.
In addition, there are 3 examples in part 2.
Thus, the School has finished instructing you how to use the Sumproduct function in webgiaidap.com.
We hope that the sharing of the School will be of great help to you.
If you have any difficulties, please email the School at email: webgiaidap.com.vn
gmail.com
ADVANCED webgiaidap.com JOB COURSE FOR MASTERS.
Or if you have a need to learn more about the sumproduct function and other webgiaidap.com functions under the guidance and instruction of an experienced person.
You just need to send an email to the above email box and clearly state your wishes.
The school will consult specifically as well as arrange a teacher to guide you wholeheartedly.
See also: What is Absence – Meaning of Absence in Vietnamese
It’s quite late, so Truong needs to go to sleep, Truong is very determined to finish this article after a long day. Support customers using the webgiaidap.com.vn Software.
Source: internet