COUNTIF Function in Excel with Example

COUNTIF ()

This function counts the number of items which match single criteria set by the user. 

Best view in desktop mode.

	A	B	C	D	E
1	Item	Date	Price
2	Tyre	12-Feb-20	$                 10.00
3	Brake	12-Feb-20	$                 25.00
4	Seat	12-Feb-20	$               120.00
5	Tyre	13-Feb-20	$                 20.00
6	Seat	15-Feb-20	$               150.00
7	Seat	17-Feb-20	$                 50.00
8	Handle	1-Mar-20	$                 30.00
9	Mirror	1-Mar-20	$               120.00
10	Mirror	5-Mar-20	$                 60.00
11
12
13	Example	Result
14	Sales Over $50	4	=COUNTIF(C2:C10,">50")
15	Total Seat Sales	3	=COUNTIF(A2:A10,"Seat")
16	Sales before 1-Mar-20	6	=COUNTIF(B2:B10,"<1-Mar-20")
17

COUNTIF can be used to count cells with number, date, and text value that match the criteria.

COUNTIF Function supports logical operators (>,<,<>,=)

Syntax

=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)

The criteria can be typed in any of the following ways.

To match a specific number type the number, such as =COUNTIF(C2:C10,30)

To match a piece of text type the text in quotes, such as =COUNTIF(A2:A10,"Mirror")

To match using operators surround the expression with quotes, such as =COUNTIF(C2:C10,">50")

Formatting

No special formatting is needed.

Functions for Counting

Ø  To count only numbers, use COUNT()

Ø  To count numbers and text value, use COUNTA()

Ø  To count based on one criteria, use COUNTIF()

Ø  To count based on multiple criteria, COUNTIFS()

Ø  To count blank cells, use COUNTBLANK()

COUNTA in Excel

<<  Previous

Next  >>

COUNTIFS in Excel