Summary of usage of COUNTIF function in Excel

This article brings you relevant knowledge about excel, which mainly introduces related issues about the COUNTIF function, including how to enter different serial numbers, how to calculate unique number of people, etc. Let’s take a look at the content below, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

1. Different serial numbers

As shown below, To enter serial numbers by department in column A, the serial numbers of different departments start from 1.

Enter the formula in cell A2 and copy it down:

=COUNTIF(B$2:B2,B2)

COUNTIF function The statistical area is B$2:B2, the first B2 is an absolute reference to the row, and the second B2 is a relative reference. When the formula is copied downward, it will become B$2:B3, B$2:B4... an ever-expanding area, and the number of departments in column B is counted from this dynamic area.

2. Calculate the number of unique people

As shown in the figure below, calculate the number of unique people in column C.

The formula is:

=SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14))

This is a ten The commonly used formula for counting the number of non-repeating data contains a simple mathematical logic:

Any data repeated N times, the sum of N 1/N is 1.

The "COUNTIF(A2:A14,A2:A14)" part of the formula is an array calculation, and its function is to count the number of occurrences of each element in the cell range A2:A14.

The operation process is equivalent to:

=COUNTIF(A2:A14,A2)

=COUNTIF(A2:A14,A3)

……

=COUNTIF(A2:A14,A14)

The returned memory array result is:

{2;2;2;2;2;2;2;2;2 ;2;1;1;1}

Then divide the returned memory array by 1 to get the following result:

{0.5;0.5;0.5...;1;1;1}

Dividing by 1 is equivalent to calculating the reciprocal of the memory array returned by the COUNTIF function.

For ease of understanding, the decimal part in the result of this step is replaced by a fraction. The result is:

{1/2;1/2;1/2;1/2;… …;1;1;1}

If the cell value is the only value in the range, the result of this step is 1.

If repeated twice, the result of this step will be two 1/2.

If the value of the cell appears three times in the range, the result will be 3 1/3...

That is, the total result of the reciprocal corresponding to each element is still 1.

Finally, use the SUMPRODUCT function to calculate the total number of unique people.

3. Extract the non-duplicate list

As shown in the figure below, you need to extract the non-duplicate list in column C.

The formula is:

=INDEX(C:C,1 MATCH(0,COUNTIF(E$1:E1,C$2:C$15),0))&””

Note that this is an array formula. After editing is completed, press Ctrl Shift and press Enter.

First use the COUNTIF function to find the number of each data in the cell range C$2:C$15 in the cell range above the position of the formula. Returns an array composed of 0 and 1. If the element in the C$2:C$15 cell range appears above the formula, the result is 1; if it does not appear, the result is 0.

As in the first example, the first parameter of the COUNTIF function is an extended range, and the extracted results of the formula will be reused.

Then use the MATCH function to find the position of the first 0 in the array returned by the COUNTIF function, that is, to find the position of the first occurrence of the data.

Since the title row of the data table occupies 1 row, adding 1 to this number is the column position of the unique data that needs to be extracted in the data table.

Then use the INDEX function and use the calculation result of the MATCH function as the index value to extract the data at the corresponding position in column C.

This function is easier to understand if you look up from the end.

Related learning recommendations: excel tutorial

The above is the detailed content of Summary of usage of COUNTIF function in Excel. For more information, please follow other related articles on the PHP Chinese website!