What is countif function?

The Countif function is a function in Microsoft Excel that counts cells in a specified range that meet specified conditions. It can be used in WPS, Excel2003 and Excel2007 and other versions.

The syntax rules of this function are as follows:

countif（range，criteria）

Parameters: range The range in which the number of non-empty cells is to be calculated

Parameters: criteria defined in the form of numbers, expressions or text

Usage

1. Find the individual values ​​of various types of cells Number

(1) Find the number of vacuum cells: =COUNTIF(data area,"")

(2) Find the number of non-vacuum cells: =COUNTIF(data area ,"<>") is equivalent to the counta() function

(3) Number of text-type cells: =COUNTIF(data area,"*") False empty cells are also text-type cells

(4) Number of all cells in the range: =COUNTIF(data area, "<>"""), if there is " in the data area, this formula is not valid.

( 5) The number of cells with a logical value of TRUE = COUNTIF (data area, TRUE)

2. Find the number of cells that are greater than or less than a certain value

(1) Greater than 50=COUNTIF(data area,">50")

(2) Equal to 50=COUNTIF(data area,50)

(3) Less than 50 =COUNTIF( Data area, "<50")

(4) Greater than or equal to 50 =COUNTIF(Data area, ">=50")

(5) Less than or equal to 50 =COUNTIF (data area, "<=50")

(6) Greater than the value of cell E5=COUNTIF(data area,">"&$E$5)

(7) Equal to the value of cell E5=COUNTIF(data area,$E$5)

(8) Less than the value of cell E5=COUNTIF(data area,"<"&$E$5)

(9) Greater than or equal to the value of cell E5=COUNTIF(data area,">="&$E$5)

(10) Less than or equal to the value of cell E5=COUNTIF(data Area,"<="&$E$5)

3. Number of cells equal to or containing N specific characters

(1) Two Characters=COUNTIF(data area,"??")

(2) Two characters and the second one is B=COUNTIF(data area,"?B")

(3) Contains B =COUNTIF(data area, "*B*")

(4) The second character is B =COUNTIF(data area, "?B*")

(5) Equal to "Hello"=COUNTIF(data area,"Hello")

(6) Contains the content of cell D3=COUNTIF(data area,"*"&D3&"*")

(7) The second word is the content of cell D3 =COUNTIF(data area,"?"&D3&"*")

Note: The countif() function is not case-sensitive for English letters, and wildcards are only for The text is valid

4. Two conditions to find the number

(1)>10 and<=15 =SUM(COUNTIF(data area,"> "&{10,15})*{1,-1}) or =SUM(COUNTIF(data area,"<="&{10,15})*{-1,1})

(2)>=10and<15 =SUM(COUNTIF(data area,">="&{10,15})*{1,-1}) or =SUM(COUNTIF(data area," <"&{10,15})*{-1,1})

(3)>=10 and<=15 =SUM(COUNTIF(data area,{">=10 ",">15"})*{1,-1})

(4)>10and<15 =SUM(COUNTIF(data area,{">10","> ;=15"})*{1,-1}) or =SUM(COUNTIF(data area,{">10","<15"}))-number of samples

Note: General Multi-condition counting uses the SUMPRODUCT function. The above method is rarely used and is for reference only.

Supplement: Count in three areas: >=60 in three areas =SUM(COUNTIF(INDIRECT({"a46:a48","b48:b50","c47:c48"})," >=60"))

Supplement: (Set operation method)

The statistical range, for example, 5<= x <=7

can be decomposed into (x> =5)-(x>7)

Write as a function:

=countif(range,">=5")-countif(range,">7")

In this way, you can use the operation knowledge of sets. It also achieves the purpose of finding the same number in the above two conditions, and is simpler and easier to understand.

The above is the detailed content of What is countif function?. For more information, please follow other related articles on the PHP Chinese website!