COUNTIFS Function in Excel with Example

COUNTIFS ()

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

Best view in desktop mode.

	A	B	C	D
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	Seat sales Over $50	2	=COUNTIFS(A2:A10,"Seat",C2:C10,">50")
15	12-Feb Sales Over $100	1	=COUNTIFS(B2:B10,"12-Feb-20",C2:C10,">100")
16	Seat sales after 15-Feb-20	1	=COUNTIFS(A2:A10,"Seat",B2:B10,">15-Feb-20")
17

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

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

Syntax

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

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

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

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

To match using operators surround the expression with quotes, such as =COUNTIFS(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()

COUNTIF in Excel

<<  Previous

Next  >>

COUNTBLANK