Excel function sumproduct application case and sumproduct application case

Source: Internet
Author: User
Tags sumif function

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.