The application of SUMIF, COUNTIF and SUMPRODUCT functions in performance statistics

In the school's academic year exam examination work, in order to facilitate the marking process and avoid students cheating, often the same grade different classes of students to separate mixed. After the examination is completed, how to do not change the original form of the status of the class score statistics? With the three functions sumif, countif and sumproduct, everything will be very easy. The following examples illustrate.

First, the book on the score

In the fascicle usually has the class, the examination number, the name and the language, the mathematics and so on discipline name, we may put this worksheet to establish in the Sheet1, and renamed it "the book fascicle", will enter the student achievement into it (see figure I). The Sheet2 is named "Statistical table", in which the class, subject, average score, passing number, pass rate, eugenic number, eugenics rate and other statistical information column headings, enter the class and need to count the subject content (see figure II).

Second, the establishment of functions

In the tab, enter the array formula for the C2 cell (=sumif!). $A $2:a$13, "one (1)", in fascicle! $D $2:d$13), holding down the CTRL and SHIFT keys, you can calculate the total score for one (1) class, copy the formula down to each cell, and place the One (1) "Class is changed to the corresponding class name respectively." The meaning of this array formula is to look up the results of one (1) class in the "fascicle" worksheet and return the sum of all the grades of one (1) class. As shown in Figure three

In the "tab" D2 cell input formula =round (c2/countif! $A $2:a$13, Statistics! A2), 2 calculates the average score of one (1) class, and drags the formula down to the corresponding cell. The meaning of the formula is to divide the total score in the C2 by the same number as the A2 shown in column A (1) and retain two decimal places.

Enter the array formula in the E2 to calculate the passing number. The array function is =sumproduct (login fascicle!). $A $2:a$13= Statistics! $A 2) * (in the fascicle!) $D $2:d$13>=60)), the implication is that "one (1) class" Should have the same information as "Statistical tables" in column A of the "A2" worksheet, Returns the number of persons who are greater than or equal to 60 points in the results corresponding to "one (1) classes" (Figure IV). When you copy the formula down, enter the formula in cell F2! $A $2:a$13, Statistics! $A 2) Calculates the passing rate of the class.

Copy the formulas in the E two columns to column G, and modify the formula ">=60" to the standard of eugenics (if 80 is divided into eugenic, then 60 to 80) (Figure V). Similarly, the formula in column F is copied to the H column to obtain the statistical results of the eugenic rate.

The need to remind everyone is that using an array function, must not forget is ctrl+shift+ carriage return.