Excel function sumproduct application case and sumproduct application case
Author: iamlaosong
The sumproduct function is more and more useful. In the past, it was troublesome to use the sum group function to replace and copy the function. sumif is inconvenient when there are many conditions. Now it's much easier to use the sumproduct function. After checking the usage of the sumproduct function, it is interpreted as "finding the sum of the product of two or more arrays". If so, one-sided geographic solution is irrelevant to the summation of multiple conditions, that's wrong. In fact, the sumproduct function is much easier to use than the sumif function by using true and false values (1 for "true" and 0 for "false. For example, "= SUMPRODUCT (A1: A100 =" engineer ") * 1)" counts the number of lines containing "engineers" in column A. Note that, "* 1" is indispensable. Otherwise, the result is zero. The following are several application cases.
1. inventory statistics
The Excel file contains three tables, one warehouse receiving record, one warehouse picking record, and one inventory record. After the warehouse receiving record is added, the inventory table uses the sumproduct function to automatically reflect the current inventory.
(1) columns A, B, C, D, E, and F
Warehouse receiving time |
Document No. |
Part number |
Cargo quantity |
Packaging type |
Quality Status |
2013-06-04 |
A1307703 |
F114001721 |
72 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001722 |
36 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001723 |
72 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001724 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001725 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001726 |
120 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001727 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307703 |
F114001728 |
24 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001729 |
144 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001730 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001731 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001732 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001733 |
72 |
Bin |
Qualified |
2013-06-04 |
A1307708 |
F114001734 |
72 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001728 |
144 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001729 |
144 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001730 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001731 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001732 |
48 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001733 |
144 |
Bin |
Qualified |
2013-06-04 |
A1307714 |
F114001734 |
144 |
Bin |
Qualified |
2013-06-04 |
A1307719 |
F114001735 |
72 |
Bin |
Qualified |
2013-06-04 |
A1307719 |
F114001736 |
36 |
Bin |
Qualified |
2013-06-04 |
A1307719 |
F114001737 |
144 |
Bin |
Qualified |
(2) columns A, B, C, D, E, and F
Warehouse Picking Time |
Document No. |
Part number |
Cargo quantity |
Packaging type |
Quality Status |
2013-06-06 |
0000003 |
F114001721 |
576 |
Bin |
Qualified |
2013-06-06 |
0000005 |
F114001722 |
192 |
Bin |
Qualified |
2013-06-06 |
0000006 |
F114001723 |
240 |
Bin |
Qualified |
2013-06-06 |
0000007 |
F114001724 |
252 |
Bin |
Qualified |
2013-06-07 |
0000008 |
F114001725 |
288 |
Bin |
Qualified |
2013-06-07 |
0000008 |
F114001726 |
288 |
Bin |
Qualified |
2013-06-07 |
0000008 |
F114001727 |
144 |
Bin |
Qualified |
2013-06-07 |
0000009 |
F114001728 |
432 |
Bin |
Qualified |
2013-06-07 |
0000010 |
F114001729 |
216 |
Bin |
Qualified |
2013-06-07 |
0000010 |
F114001730 |
360 |
Bin |
Qualified |
2013-06-07 |
0000010 |
F114001731 |
144 |
Bin |
Qualified |
2013-06-07 |
0000011 |
F114001732 |
144 |
Bin |
Qualified |
2013-06-07 |
0000012 |
F114001733 |
72 |
Bin |
Qualified |
2013-06-07 |
0000013 |
F114001734 |
360 |
Bin |
Qualified |
2013-06-07 |
0000014 |
F114001728 |
120 |
Bin |
Qualified |
2013-06-07 |
0000016 |
F114001729 |
72 |
Bin |
Qualified |
2013-06-07 |
0000016 |
F114001730 |
118 |
Bin |
Qualified |
2013-06-07 |
0000016 |
F114001731 |
144 |
Bin |
Qualified |
2013-06-07 |
0000016 |
F114001732 |
144 |
Bin |
Qualified |
2013-06-08 |
0000018 |
F114001733 |
72 |
Bin |
Qualified |
2013-06-08 |
0000018 |
F114001734 |
72 |
Bin |
Qualified |
2013-06-08 |
0000019 |
F114001735 |
216 |
Bin |
Qualified |
2013-06-08 |
0000019 |
F114001736 |
216 |
Bin |
Qualified |
2013-06-08 |
0000020 |
F114001737 |
192 |
Bin |
Qualified |
(3) columns A, B, C, D, E, F, and G in the inventory record table
Part number |
Goods name |
Packaging type |
Quality Status |
Total warehouse receiving |
Total warehouse picking |
Inventory quantity |
F114001721 |
YN3 HousingLH |
Bin |
Qualified |
1116 |
1564 |
72 |
F114001722 |
YN3 HousingRH |
Bin |
Qualified |
492 |
1163 |
36 |
F114001723 |
YN3 LensLH |
Bin |
Qualified |
668 |
2295 |
72 |
F114001724 |
YN3 LensRH |
Bin |
Qualified |
600 |
2183 |
48 |
F114001725 |
YP7 HousingLH |
Bin |
Qualified |
312 |
1152 |
48 |
F114001726 |
YP7 HousingRH |
Bin |
Qualified |
624 |
926 |
120 |
F114001727 |
YP7 LensLH |
Bin |
Qualified |
1008 |
636 |
48 |
F114001728 |
YP7 LensRH |
Bin |
Qualified |
600 |
2308 |
24 |
F114001729 |
T61 HousingLH |
Bin |
Qualified |
648 |
1352 |
144 |
F114001730 |
T61 HousingRH |
Bin |
Qualified |
264 |
1368 |
0 |
F114001731 |
T61 LensLH |
Bin |
Qualified |
228 |
954 |
0 |
F114001732 |
T61 LensRH |
Bin |
Qualified |
180 |
1480 |
0 |
F114001733 |
T63 HousingLH |
Bin |
Qualified |
520 |
1048 |
0 |
F114001734 |
T63 HousingRH |
Bin |
Qualified |
444 |
1138 |
0 |
(4) inventory calculation formula
Total warehouse receiving: = SUMPRODUCT (warehouse receiving! $ C $2: $ C $65535 = inventory! $ A2) * (warehouse receiving! $ E $2: $ E $65535 = inventory! $ C2) * (warehouse receiving! $ F $2: $ F $65535 = inventory! $ D2) * (warehouse receiving! $ D $2: $ D $65535 ))
Total warehouse picking: = SUMPRODUCT (warehouse picking! $ C $2: $ C $65535 = inventory! $ A2) * (warehouse picking! $ E $2: $ E $65535 = inventory! $ C2) * (warehouse picking! $ F $2: $ F $65535 = inventory! $ D2) * (warehouse picking! $ D $2: $ D $65535 ))
The condition determination of each number in the formula produces different "true" and "false" values. The values of the array are different "0" and "1 ", the numbers of each group are multiplied. Only the rows where the conditions are fully met can obtain numerical values. The result of adding these numeric values is the required value. This is the sum of multiple conditions for the SUMPRODUCT method. The first formula above can be described as (the second formula is the same ):
= SUMPRODUCT (warehouse receiving part number area = inventory part number) * (warehouse receiving packaging type area = inventory packaging type) * (warehouse receiving quality status area = inventory quality status) * (warehouse receiving quantity Area ))
Inventory quantity: = E2-F2
2. Daily quality notification
There are three tables in the Excel file, one quality record summary table, one daily report, and one accumulative report. After adding the daily quality record (click the button, the VBA program automatically extracts quality records from the Database Based on the date in the P1 cell). The sumproduct function is used to automatically reflect the current quality on a daily basis. The modification date. Two reports, daily report and accumulative report, immediately reflect the quality data of the day.
(1) Quality record column A, B, C, D, E, and F
Delivery Date |
Shipping Unit |
Time Limit |
Number of imported mails |
Number of unauthorized emails in a timely manner |
Number of unauthorized emails |
Timely and proper investment rate |
2014-9-1 |
Hefei |
168 |
30 |
7 |
77.98% |
2014-9-1 |
Fuyang City |
44 |
3 |
0 |
93.18% |
2014-9-1 |
Bengbu City |
40 |
1 |
0 |
97.50% |
2014-9-1 |
Wuhu City |
103 |
7 |
0 |
93.20% |
2014-9-1 |
Anqing City |
60 |
9 |
0 |
85.00% |
2014-9-1 |
Suzhou City |
45 |
4 |
0 |
91.11% |
2014-9-1 |
Chuzhou City |
73 |
1 |
0 |
98.63% |
2014-9-1 |
Lu 'an city |
52 |
1 |
0 |
98.08% |
2014-9-1 |
Huangshan city |
17 |
3 |
0 |
82.35% |
2014-9-1 |
Huaibei City |
15 |
1 |
0 |
93.33% |
2014-9 |
Bozhou city |
34 |
2 |
1 |
91.18% |
2014-9-1 |
HuaiNan City |
37 |
1 |
1 |
94.59% |
2014-9-1 |
Ma'anshan |
24 |
1 |
0 |
95.83% |
2014-9-1 |
Xuancheng District |
29 |
3 |
0 |
89.66% |
2014-9-1 |
Tongling City |
23 |
4 |
0 |
82.61% |
2014-9-1 |
Chizhou city |
21 |
3 |
0 |
85.71% |
2014-9-2 |
Hefei |
270 |
109 |
8 |
56.67% |
2014-9-2 |
Fuyang City |
62 |
7 |
0 |
88.71% |
2014-9-2 |
Bengbu City |
51 |
1 |
0 |
98.04% |
2014-9-2 |
Wuhu City |
107 |
10 |
0 |
90.65% |
2014-9-2 |
Anqing City |
72 |
5 |
0 |
93.06% |
(2) Report columns A, B, C, D, E, and F on A daily basis.
Shipping Unit |
Time Limit |
Number of imported mails |
Number of unauthorized emails in a timely manner |
Number of unauthorized emails |
Timely and proper investment rate |
Hefei |
168 |
30 |
7 |
77.98% |
Fuyang City |
44 |
3 |
0 |
93.18% |
Bengbu City |
40 |
1 |
0 |
97.50% |
Wuhu City |
103 |
7 |
0 |
93.20% |
Anqing City |
60 |
9 |
0 |
85.00% |
Suzhou City |
45 |
4 |
0 |
91.11% |
Chuzhou City |
73 |
1 |
0 |
98.63% |
Lu 'an city |
52 |
1 |
0 |
98.08% |
Huangshan city |
17 |
3 |
0 |
82.35% |
Huaibei City |
15 |
1 |
0 |
93.33% |
Bozhou city |
34 |
2 |
1 |
91.18% |
HuaiNan City |
37 |
1 |
1 |
94.59% |
Ma'anshan |
24 |
1 |
0 |
95.83% |
Xuancheng District |
29 |
3 |
0 |
89.66% |
Tongling City |
23 |
4 |
0 |
82.61% |
Chizhou city |
21 |
3 |
0 |
85.71% |
Accrued |
785 |
74 |
9 |
89.43% |
Calculation formula (cell P1 stores the notification date ):
Number of imported mails: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 = $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! C $4: C $500 ))
Number of delayed messages: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 = $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! D $4: D $500 ))
Number of unauthorized Emails: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 = $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! E $4: E $500 ))
Timely proper investment rate: = (B4-C4-D4)/B4
(3) Accumulative notification
The content is the same as the daily report, but the data is accumulated for the current month. Therefore, the formulas for each field are similar, but there is an additional less than the symbol "<", that is:
Number of imported mails: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 <= $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! C $4: C $500 ))
Number of delayed messages: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 <= $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! D $4: D $500 ))
Number of unauthorized Emails: = SUMPRODUCT (Data Summary! $ A $4: $ A $500 <= $ P $1) * (Data Summary! $ B $4: $ B $500 = $ A4) * (Data Summary! E $4: E $500 ))
Timely proper investment rate: = (B4-C4-D4)/B4
The meaning of the formula is not explained, that is, the multi-condition summation. The conditions here can be in multiple forms, equal to, greater than, less than, and not equal to all rows, as long as you remember the true and false values: true = 1, false = 0.
Question about the SUMPRODUCT function in EXCEL
About Formulas
= SUMPRODUCT (1 * (order list! E3: E262 = "MS Office advanced application"), order list! H3: H262)
Interpretation
"MS Office advanced application" is a string,
Order list! E3: E262 = "MS Office advanced application" ------- compare the E3: E262 region in the order list with the string to see if it is consistent. True is returned for the same value, and false is returned for different values. Get 200 false values.
1 * Two hundred genuine values -------- get 200 1 and 0. True to 1, false to 0
Sumproduct (two hundred items 1 and 0, order details! H3: H262) is used to calculate the sum of column H in the "MS Office advanced application" column E in the order list.
Therefore, the formula can also be written
= Sumif (order list! E3: E262, "MS Office advanced application", order list! H3: H262)
Application of the sumproduct function in excel
F1 help