How to use COUNTIF functions in Excel

How to use COUNTIF functions in Excel

The COUNTIF function is a calculated number of functions, through the COUNTIF function, you can calculate the number of cells in the calculation area that meet the given conditions. This function is simple and widely used. Next, let's learn how to use the COUNTIF function in Excel.

The COUNTIF function has two basic syntaxes: range, criteria. Range is one or more cells that you want to count, including numbers or names, arrays, or references that contain numbers. Null values and text values are ignored.

Criteria is a condition that determines which cells will be counted, in the form of a number, expression, cell reference, or text. For example, a condition can be expressed as 32, "<32", ">32", "apple", or B4.

The COUNTIF function has 16 formula settings:

1, return to add the number of cells containing the value 12

=countif (data,12)

2, return the number of cells containing negative values

=countif (DATA, "<0")

3, return is not equal to 0 of the number of cells

=countif (DATA, "<>0")

4, return the number of cells greater than 5

=countif (DATA, ">5")

5, returns the number of cells equal to the content in cell A1

=countif (DATA,A1)

6, returns the number of cells greater than the content in cell A1

=countif (DATA, ">" &A1)

7, return the number of cells containing text content

=countif (DATA, "*")

8, return the number of cells containing three characters of content

=COUNITF (DATA, "???")

9. Returns the number of cells that contain the word "good" (not case-insensitive) content

=countif (DATA, "good")

10. Returns the number of cells that contain the word "good" characters anywhere in the text

=countif (DATA, "*good*")

11, returns the number of cells containing the beginning of the word "AB" (case-insensitive)

=countif (DATA, "ab*")

12, return the number of cells containing the current date

=countif (Data,today ())

13, return the number of cells greater than the average

=countif (data, ">" &average (Data))

14. Number of cells returning values above three standard errors on average

=countif (data, ">" &average (Data) +stdev (data) *3)

15, return the number of cells containing a value of 3 or-3

=countif (data,3) +counif (data,-3)

16. Returns the number of cells containing the value and the logical value is true

=countif (Data,true)

The following is an example of how the COUNTIF function is used:

Number of students who score 90 or more:

In the C2 cell, enter the formula =countif (B2:b20, ">90"), where B2:b20 represents the statistical area, and ">90" indicates a condition with a score greater than 90.

The result is 5, consistent with the number of results in the loop.

The number of students who score greater than or equal to 80 points is less than 90.

In C4 cell Enter Formula =countif (B2:B20, ">=80")-countif (B2:b20, ">=90")

The result is 8, consistent with the result.

See the COUNTIF function is a function that evaluates by condition, the first parameter in parentheses represents the statistic region, and the second parameter represents the condition, which is counted in the result as long as the condition is met.