COUNTIF

COUNTIF(range, criteria)

Returns the number of numerical values that satisfies one condition.

range	The range of cells from which you want to count the cells.
criteria	The logical test that will filter out the data.

REMARKS

* This function is only available for backwards compatibility and should not be used.
* COUNTIFS was added in Excel 2007 to replace this function.
* When replacing this function with the COUNTIFS function, remembering to change the order of the arguments.
* This function includes hidden cells.
* This function is not case sensitive when matching text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* This function uses the actual cell values and not the displayed formatted values.
* If you are checking for numerical conditions make sure the cells contain numbers and not text.
* The "range" must be a cell range or a named range.
* The "range" does not have to be sorted into any order.
* The "criteria" can be a cell reference or a named range.
* The "criteria" can be in the form of a number, expression, or text.
* The "criteria" can be expressed as numerical (i.e. 32) or as a string (i.e. "32").
* The "criteria" can use string matching, i.e. *M* is all words that contain the letter "M". This is not case sensitive.
* The "criteria" can use string matching, i.e. ?M is all words that have the letter "M" as there second character.
* You can use the IF function to return a value based on one condition.
* You can use the COUNT function to return the number of numerical values.
* You can use the COUNTIFS function to return the number of numerical values that satisfies multiple conditions.
* This is similar to the AVERAGEIF and SUMIF functions.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C
1	=COUNTIF(B1:B5, "Jan") = 2	Jan	20
2	=COUNTIF(B1:B5, "=Jan") = 2	Feb	30
3	=COUNTIF(C1:C5, "<0") = 0	Jan	40
4	=COUNTIF(C1:C5, ">40") = 2	Feb	60
5	=COUNTIF(C1:C5, "<>0") = 5	Mar	80
6	=COUNTIF(C1:C5, "=20") = 1
7	=COUNTIF(C1:C5, ">" & C3) = 2	01 Jan 2025
8	=COUNT(B1:C6) = 5	02 Jan 2025
9	=COUNTIF(B1:C6, "*") = 5	03 Jan 2025
10	=COUNTIF(B1:C6, "B ") = 2	04 Jan 2025
11	=COUNTIF(B1:C6, "b ") = 2	05 Jan 2025
12	=COUNTIF(B1:C6, "???") = 5
13	=COUNTIF(B1:C6, "?e* ") = 2
14	=COUNTIF(B7:B11, ">02/01/2025") = 3
15	=COUNTIF(B7:B11, ">02 Jan 2025") = 3
16	=COUNTIF(B7:B11, ">"&DATE(2025, 1, 2)) = 3
17	=COUNTIF(B7:B11, "<"&TODAY()) = 0
18	=COUNTIF(B1:B5, "JAN")+COUNTIF(B1:B5, "feb") = 4
19	=COUNTIF(C1:C5, ">20")-COUNTIF(C1:C5, ">=80") = 3
20	=SUM(IF(C1:C5>20, 1, 0)*IF(C1:C5<80, 1, 0)) = 3

1 - How many of the values in the range "B1:B5" contain the value "Jan".
2 - How many of the values in the range "B1:B5" contain the value "Jan".
3 - How many of the values in the range "C1:C5" contain a negative number.
4 - How many of the values in the range "C1:C5" contain a number greater than 40.
5 - How many of the values in the range "C1:C5" contain a non zero value.
6 - How many of the values in the range "C1:C5" contain the number 20.
7 - How many of the values in the range "C1:C5" contains a number greater than 40.
8 - How many of the values in the range "B1:C8" contain numerical values.
9 - How many of the values in the range "B1:C8" contain text.
10 - How many of the values in the range "B1:C6" contain the letter "B". This is not case sensitive.
11 - How many of the values in the range "B1:C6" contain the letter "b". This is not case sensitive.
12 - How many of the values in the range "B1:C6" contain only three letters.
13 - How many of the values in the range "B1:C6" have the letter "e" as their second character.
14 - How many of the values in the range "B7:B11" contain a date greater than "02/01/2025".
15 - How many of the values in the range "B7:B11" contain a date greater than "2 Jan 2025".
16 - How many of the values in the range "B7:B11" contain a date greater than "2 Jan 2025".
17 - How many of the values in the range "B7:B11" contain a date greater than today.
18 - How many of the values in the range "B1:B5" contain the value "JAN" or "feb". This is not case sensitive.
19 - How many of the values in the range "C1:C5" contain a value greater than 20 and less than 80.
20 - How many of the values in the range "C1:C5" contain a value greater than 20 and less than 80.