Excel function sumproduct application case and sumproduct application case

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