COUNTIF function Application Case analysis

COUNTIF function Application Case analysis

The main purpose of the COUNTIF function is to count the number of cells in an area that satisfy a given condition.

First, the basic usage of the COUNTIF function:

1, the condition is "constant" case:

In the following example, the number of "departments" belonging to the "finance department" will be counted:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/80/81/wKioL1dDmDrj0I7_AACSjsxQxKE834.jpg "/>

For example, the COUNTIF parameter is:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/80/81/wKioL1dDmDrjIEmGAAAtsNjXWZI724.jpg "/>

2. The condition can be an expression:

The number of people who calculate the wage ">=1500", the formula is as follows.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/80/83/wKiom1dDl0mSHIJQAAC3gAuGg_o748.jpg "/>

3. Calling a cell reference or using a function in an expression

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/83/wKiom1dDl0mxc2PVAACvjUY6PNc145.jpg "/>

COUNTIF (C2:c8, ">=" &d11), if you put D11 in quotes, it means force "D11" into text. The true meaning of the formula becomes: in the C2:c8 area, the number of cells with a value of ">=d11" is obtained.

From this, you can also generate a comparison value from a formula or function, as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/80/81/wKioL1dDmDvBUz7-AACssV2lUKQ352.jpg "/>

4. Using wildcard characters

The COUNTIF function condition can include the wildcard character "*" or "? "

* represents any number of arbitrary characters

？ Represents an arbitrary character

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/81/wKioL1dDmDyxFpvCAACwCDp2l0w258.jpg "/>

Second, the application case:

1, not allowed to enter duplicate values

The commodity price list for an e-commerce company requires that the commodity code be unique. Click "A" with your mouse and select column A.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/83/wKiom1dDl0qCdxrOAAB4ECLgLmg974.jpg "/>

Then click the Data tab, locate data validation in the Data Tools group, and then select Data validation after clicking. The validation criteria option allows custom, such as the formula.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/80/83/wKiom1dDl0qzKKc-AACnNsJePDw289.jpg "/>

2, check the data of the two worksheets

, check which data in table B appears in table A, where we add a "secondary column". The principle of using countif, that is, the number of each product name in table B appears in table A, if not equal to 0, indicates a common existence, equals 0 indicates that there is no.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/80/81/wKioL1dDmD3yLlGUAABVp9a7JUg619.jpg "/>

After entering the formula below, copy the formula to the other cells in column B to get the following result:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/80/83/wKiom1dDl0uyEB5WAABlIe-_JLA864.jpg "/>

In this example, we find that "1" is a common (or identical) data.

Study Questions

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/80/81/wKioL1dDmD3wljsGAACmxQM9tO0543.jpg "/>

Long Press the QR code to join Office Workplace Training

Upgrade to high-force office talent Now

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/83/wKiom1dDl0zz_uv8AADR7F1lmFM579.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/81/wKioL1dDmD7BA4g5AADSIz8MO3M751.jpg "/>

A piece of work, a harvest

Good knowledge Music sharing

COUNTIF function Application Case analysis