COUNTIF

Counts the number of cells in a range that meet a specified condition.

Syntax:

COUNTIF(test_range, condition)

test_range is the range to be tested. condition may be:

a number, such as 34.5
an expression, such as 2/3 or SQRT(B5)
a text string

COUNTIF counts those cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:

>, <, >=, <=, =, <>

In this case COUNTIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).

For example the condition “>4.5” tests if the content of each cell is greater than the number 4.5, and the condition “<dog” tests if the content of each cell would come alphabetically before the text dog.

Blank (empty) cells in test_range are ignored (they never satisfy the condition). condition can only specify one single condition.

Example:

COUNTIF(C2:C8, ">=20")

returns the number of cells in C2:C8 whose contents are numerically greater than or equal to 20.

COUNTIF(C2:C8, F1)

where F1 contains the text >=20, returns the same number.

COUNTIF(C2:C8, "<"&F2)

where F2 contains 20 returns the number of cells in C2:C8 whose contents are numerically less than 20. (Advanced topic: this works because the & operator converts the content of F2 to text, and concatenates it with "<"; COUNTIF then converts it back to a number).

COUNTIF(A2:A8, ">=P")

returns the number of cells in A2:A8 whose contents begin with the letter P or later in the alphabet.

COUNTIF(B2:B8, "red")

returns the number of cells in B2:B8 containing red, but this number may depend on the option settings discussed above.

Advanced topic:

COUNTIF(B2:B8, ".+")

returns the number of cells in B2:B8 containing one or more character, e.g. not blank, using the syntax of regular expressions.

SUMPRODUCT(B2:B8="Red").

returns the number of cells in B2:B8 matching Red, with case sensitivity.

Application:

Let's imagine a small business owner, Sarah, who runs a local bakery. She wants to analyze her sales data for the past week to see which of her top-selling items are performing the best. She uses a spreadsheet to track her daily sales.

Here is a simplified version of her sales data in a table:

	Date	Item Sold	Quantity
	A	B	C
1	8/1/2025	Croissant	12
2	8/1/2025	Baguette	5
3	8/2/2025	Croissant	8
4	8/2/2025	Danish	6
5	8/3/2025	Danish	10
6	8/3/2025	Croissant	15
7	8/4/2025	Baguette	7
8	8/5/2025	Croissant	20
9	8/6/2025	Baguette	10
10	8/7/2025	Danish	12
11	8/7/2025	Baguette	8

Sarah wants to find out how many times each item was sold during the week. This is where the COUNTIF function comes in handy.

To count how many times "Croissant" appears in the "Item Sold" column, she would use the following formula:

COUNTIF(B1:B11, "Croissant")

B1:B11 is the range of cells where the data she wants to count is located.
"Croissant" is the criteria she is looking for.

The result of this formula would be 4, because "Croissant" appears in the list four times.

She can then do the same for her other items:

For "Baguette": COUNTIF(B1:B11, "Baguette") which would result in 4.
For "Danish": COUNTIF(B1:B11, "Danish") which would result in 3.

By using COUNTIF, Sarah can quickly see the frequency of sales for each item and make decisions about her inventory and production schedule. She can see that croissants and baguettes are equally popular in terms of the number of days they were sold, while Danish pastries were sold on fewer days.

Result for Croissant:

4

Result for Baguette:

4

Result for Danish:

3