At the end of each year, the year-end statistics are indispensable. For some complex reports, such as civil servant statistics (Figure 1), due to the balance between the table and the table, if you fill in the careless, the report is not balanced, and it is time-consuming and laborious to use manual statistics. With Excel PivotTable, it is impossible to complete a variety of classification statistics, such as education, age, and so on. Xiaobian recommended "Office2015 download address" civil servant statistical report example [solving ideas] In fact, there is a simple method, as long as the maintenance of "people list", the worksheet can be, the other statistical reports in the worksheet automatically generated and ensure balance . In the future, if there is any change in personnel and personnel information, just change the “personnel list” and the worksheet, and the statistical reports in other worksheets will be automatically changed and balanced, and will be changed once and for all. [Solution Method] In the Excel table, the function of the SUMPRODUCT function is to multiply the corresponding elements between the arrays in a given array of arrays and return the sum of the products. Think of a set of arrays as a condition, and multiple arrays can achieve multiple conditional counts. “The list of people” is shown in the figure (Figure 2). “Personnel List" Worksheet Structure To achieve the count of the township-level staff in the personnel list, you can fill in the following formula: SUMPRODUCT((Person List!$E$2:$E$1000="乡级级职职" ;)*1). The purpose of multiplying 1 is to change the logical judgment value into a numerical value, which is 1 for the township level, 0 for the non-conformity, 0 for the sum, and 1000 for the person list, and more than 1000 people. Insert rows in the list to increase. If you want more than one condition, add a set of conditions. If you want to count the number of undergraduate students in the township level in the list of people, fill in the following formula: SUMPRODUCT((person list!$E$2:$E$1000=" ; township level official position ") * (person list! $G$2: $G$1000 = " university undergraduate ")). Add conditions, and so on. Enter the formula in turn in the worksheet “County". In order to improve the efficiency of the input formula, you can refer to the table header row and column, such as university undergraduate, township township, etc., so you can drag the formula after filling out a line of formula. For example, in the “County” table, you can fill in the following formula: SUMPRODUCT((Person List!$E$2:$E$1000=$A13)*(Person List!$G$2:$ G$1000=L$3)*(list of people!$F$2:$F$1000="County")). $A13 is equal to the township level, and L$3 is equal to the undergraduate degree. This formula can be dragged up and down, and all the academic cells are filled out. Fill in the other worksheet formulas. Since the basic situation of civil servants in cities, counties, and townships is basically the same, after completing the “County County” worksheet, you can copy the “County County” worksheet formula to the “City” worksheet and replace it with the search. The function replaces all the counting conditions with the county's totals into the prefecture (Fig. 3). The “city” worksheet formula is completed. The forms of townships, women, ethnic minorities, and non-Parties, and so on, can quickly complete the formula filling of other worksheets. This article comes from [System Home] www.xp85.com