WPS Table Tutorial: sumproduct function Application Two

The role of the sumproduct function is to multiply the corresponding elements of an array in a given set of arrays and return the sum of the products. Its syntax format is sumproduct (data 1, data 2, ..., data 30), such as the formula "=sumproduct (A2:A6,B2:B6,C2:C6)", which acts as "=a2*b2*c2+a3*b3*c3+a4*b4*c4+a5* B5*c5+a6*b6*c6 ", that is to say, the formula is three regions A2:a6, B2:B6, C2:C6 peer data product and.

I. Statistics duplicate identity card number

Typically, the number of times that statistical data appears within a range of cells is countif. But if it's used to count 18 ID numbers, there's a problem: as long as the first 15 digits are the same, the Kingsoft table will take it as the same number (as in Excel). One way to solve this problem is to use the Sumproduct function instead of countif.

As shown in Figure 1.

Figure 1

You can get the number of times each data appears in column B as long as you enter the formula =sumproduct ($B $ $B *1) in the C2 cell, and then drag the cell fill handle to copy the formula down to the last line. Those that are greater than 1 are repetitive.

The formula can get the number of cells that are equal to the B2 cell data in the b2:b3000 range of cells. The result is a series of logical values such as "true, false, True, ...", "true" equals "1", "FALSE" equals "0", and "true" The sum of the numbers.

II. Multi-Conditional statistical data

Figure 2

As shown in the table in Figure 2, if we want to make a statistic of the "total number of overtime for male teachers in Grade One" in the table, how do we do that? In fact, a task like this multiple conditional sum is suitable for sumproduct functions.

You only need to enter the following formula in the cell: "=sumproduct (($C $: $C $15=" man ") * ($D $: $D $15=" Tall ") + ($D $: $D $15=" sophomore ") * ($E $: $E $15=" Middle One "), $G $: $G $ $F $: $F $) ", you can get the results you want by pressing the ENTER key. Very convenient.

Formula "($D $: $D $15=" Senior ") + ($D $: $D $15=" Senior ")" means one of the conditions for "senior or senior", combined with the rest of the conditions can be met the conditions of "senior or senior grade secondary male teacher" cell. The last two parameters in the formula can get the product of the data of the peer data f and G column, and finally the product is summed up by the formula, so our task is complete.