Tanabata romantic date "Hui"
Super Member Lifetime Card 23% off for a limited time
The original price is 168. Sign up today for only 129!
Don't miss it. ヾ(◍°∇°◍)ﾉﾞ
The function SUMPRODUCT is a function that integrates counting and summation. In the array formula, the function SUMPRODUCT can replace the function SUM without pressing the three keys of <Ctrl+Shift+Enter> to end.
Function definition:
In the given sets of arrays, multiply the corresponding elements between the arrays and return the sum of the products.
Syntax format:
SUMPRODUCT(array1, [array2], [array3], ...)
array1: Required. The first array argument whose corresponding elements need to be multiplied and summed.
array2, array3,...: optional. 2 to 255 array arguments whose corresponding elements need to be multiplied and summed.
1. Ignore text summation
Enter the formula:
=SUMPRODUCT(A2:A10)
The function SUMPRODUCT treats non-numeric array elements as 0.
The formula is SUMPRODUCT({1;0;2;0;3;0;0;0;5}), which returns 11
2. Summation of textual values
Enter the formula:
=SUMPRODUCT(B2:B9*1)
The value of cells B2:B9 is text type, *1 is to convert the text type value to numeric type
Can be converted with "--", "+0", "-0", "/1", "^1", etc.
3. Single condition counting
Find the number of performances greater than or equal to 5000:
Enter the formula:
=SUMPRODUCT(N(B2:B9>=5000))
B2:B9>=5000 part returns {FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}
If the condition is satisfied, return the logical value TRUE, if the condition is not satisfied, return the logical value FALSE
Function N: Converts a value that is not in numeric form to numeric form. Dates are converted to serial values, TRUE is converted to 1, and other values are converted to 0.
Similarly, "--", "+0", "-0", "/1", "^1", "*1", etc. can be used to convert
N(B2:B9>=5000) part returns {0;1;1;0;0;1;1;0}
Summing with function SUMPRODUCT returns 4
4. One-condition summation
Find the sum of performance greater than or equal to 5000:
Enter the formula:
=SUMPRODUCT((B2:B9>=5000)*B2:B9)
(B2:B9>=5000) part returns {FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}
When four arithmetic operations occur, TRUE is equivalent to 1, and FALSE is equivalent to 0
(B2:B9>=5000)*B2:B9 part returns {0;7800;6521;0;0;16660;8054;0}
Summing with function SUMPRODUCT returns 39035
5. Multi-condition counting
Find the number of sales of a unit whose performance is greater than or equal to 5000:
Enter the formula:
=SUMPRODUCT((B2:B9="Sell one")*(C2:C9>=5000))
Multi-condition counting syntax:
SUMPRODUCT((ConditionArea1=Condition1)* (ConditionArea2=Condition2)* (ConditionAreaN=ConditionN))
6. Multi-conditional summation
Find the sum of a sales volume greater than or equal to 5000:
Enter the formula:
=SUMPRODUCT((B2:B9="Sell one unit")*(C2:C9>=5000)*C2:C9)
Multi-conditional summation syntax:
SUMPRODUCT((Condition Area 1=Condition 1)* (Condition Area 2=Condition 2)*(Condition Area N=Condition N)*Sum Area)
or:
SUMPRODUCT((Condition Area 1=Condition 1)*(Condition Area 2=Condition 2)*(Condition Area N=Condition N),Sum Area)
This question can also use the formula:
=SUMPRODUCT((B2:B9="Sell one")*(C2:C9>=5000),C2:C9)
The difference between using "*" and "," before the summation condition in the multi-conditional summation syntax:
When there is no text in the summation area, "*" and "," can be used interchangeably;
Use "," when there is text in the summation area, and return the error value #VALUE!
7. Calculate the number of non-repeated counts with a single condition
Find the number of non-repeated salespersons:
Enter the formula:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
COUNTIF(A2:A9,A2:A9) part counts the number of times each cell content in the range A2:A9 appears in this range, and returns {2;1;2;3;1;1;3;3}
1/{2;1;2;3;1;1;3;3} is to count the repeated data as 1, for example, 2 times is 1/2, 3 times is 1/3,
1/COUNTIF(A2:A9,A2:A9) part returns
{0.5;1;0.5;0.333333333333333;1;1;0.333333333333333;0.333333333333333}
Summing with function SUMPRODUCT returns 5
8. Interval summation
Find the sum of performance greater than or equal to 5000 and less than 15000:
Enter the formula:
=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000)*B2:B9)
or:
=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000),B2:B9)
9. Column summation
Find the total inbound and outbound:
Enter the formula:
=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3) , pull right and drop down to fill.
10. Multiply arrays
Ask for total commission:
Enter the formula:
=SUMPRODUCT(B2:B9*C2:C9)
B2:B9*C2:C9 returns the corresponding commission ratio for each performance*, that is, returns {45.06;390;326.05;136.89;2001.5;1466;402.7;138.75}
Then sum with the function SUMPRODUCT
11. Conditional array multiplication
Find the total commission of "one sales":
Enter the formula:
=SUMPRODUCT((A2:A9="Sell one")*C2:C9*D2:D9)
(A2:A9="Sell one unit") Returns TRUE if the condition is satisfied, and returns FALSE if the condition is not satisfied, namely {TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
Then *C2:C9 returns the performance of the sales department, namely {1502;7800;0;0;20015;14660;0;4625}
Then *D2:D9 returns the corresponding commission for a sale, namely {45.06;390;0;0;2001.5;1466;0;138.75}
Finally sum with the function SUMPRODUCT
12. Sum with "~"
Find the performance with "~" in the salesperson and:
Enter the formula:
=SUMPRODUCT(ISNUMBER(FIND("~",B2:B9))*C2:C9)
The function SUMPRODUCT cannot use wildcards, but it can be combined with the function FIND to complete the corresponding function
The FIND("~",B2:B9) part finds the position of "~" in each salesperson string, if there is no "~", the error value #VALUE! is returned, and this part returns {2;#VALUE!;3;#VALUE !;#VALUE!;1;#VALUE!;#VALUE!}
Function ISNUMBER: Determines whether a value is a numeric value and returns TRUE or FALSE.
ISNUMBER(FIND("~",B2:B9)) part returns {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Then *C2:C9 returns the performance of each salesperson with "~" {1502;0;6521;0;0;14660;0;0}
Finally use the function SUMPRODUCT to sum.
That's all for today's sharing. If the tutorial is useful to you, I hope you will share and support the editor! Every like and repost of yours is the motivation to support the editor to insist on originality.
Why choose us, the platform advantage is there
Advantage ①: VIP can learn all tutorials, unlimited learning + supporting material source files + unlimited downloads
Advantage 2: Support WeChat public account + applet + APP + PC website for multi-platform learning
Advantage 3: Support offline viewing, double-speed viewing, allowing you to learn anytime, anywhere
Advantage ④: VIP logo, distinguished status + independent after-sales group answers
★ FAQ ★
①Xiao Ai: Can you watch all the courses after buying VIP ?
Well, yes, including future updates (thanks for your support, we will insist on high-quality teaching updates)
②Xiao Ai: Hello, how do you study ?
Pay attention to the Aizhiqu Education Service Account, you can directly enter the official website to learn from the menu navigation, or you can enter the applet and download the APP!
Computer login to https://www.92zhiqu.com
(Mobile phone, computer learning is essential)
③Xiao Ai: what to do if you do n’t understand
Provide after-sales answers, pay to contact customer service and join the group (pay to contact WeChat customer service, just take a screenshot)
④Xiao Ai: Provide video courseware materials ?
Well, original high-definition video, these are provided (original tutorial, these are the most basic requirements)
⑤Classmate Xiao Ai: Validity period of study ?
Annual membership and lifetime membership, unlimited lifetime membership (self-built website + original tutorial + Aichiqu brand guarantee)
⑥ Primary school students: Does the website support accelerated viewing ?
kindness. At present, mobile phone applet + APP + computer PC all support 1.5 times accelerated viewing or slow playback
⑦ Does the course support offline download ?
kindness. Both Android and Apple IOS APP support mobile phone offline download
Computer downloads are provided by contacting WeChat customer service 18074746783.
⑧ How about the quality of the course ?
Aizhiqu Education high-quality video as the starting point, high-definition subtitles, support for audition
If there is anything else you need to consult, please contact WeChat customer service 18074746783
Long press to identify the QR code "Add Customer Service"
Click to read the original text , log in to the official website with one click, and learn from a large number of video VIPs! (you can try it out)
