COUNTIFS

Counts cells that satisfy all conditions across different ranges.

Syntax:

COUNTIFS(criteria_rangeOne, criteriaOne, [criteria_rangeTwo, criteriaTwo],…)

criteria_rangeOne is required, and the first range of cells to evaluate.

criteriaOne is required, and the condition for the first range.

[criteria_rangeTwo, criteriaTwo],… is optional, and is the additional range/criteria pairs. You can include up to 127 pairs.

Example:

If criteria_rangeOne, found in A1:A10, contains Red, Blue, Green, Yellow, Orange, Purple, Pink, Red, Black, Red, criteriaOne, found in C1, contains "Red", criteria_rangeTwo, found in B1:B10, contains Apples, Bananas, Oranges, Grapes, Mangos, Pineapples, Strawberries, Apples, Watermelons, Apples and criteriaTwo, found in C2, contains "Apples":

COUNTIFS(A1:A10, C1, B1:B10, C2)

returns 3

	A	B	C	D
1	Red	Apples	Red	3
2	Blue	Bananas	Apples
3	Green	Oranges
4	Yellow	Grapes
5	Orange	Mangos
6	Purple	Pineapples
7	Pink	Strawberries
8	Red	Apples
9	Black	Watermelons
10	Red	Apples

Application:

Suppose you are a manager at a large retail store and you want to analyze your sales data. You have a spreadsheet with the following information:

	Product Category	Region	Salesperson	Quantity Sold
	A	B	C	D
1	Electronics	North	John	5
2	Appliances	South	Jane	2
3	Electronics	South	John	3
4	Appliances	North	Jane	6
5	Electronics	North	Jane	4
6	Appliances	South	John	1
7	Electronics	North	John	7
8	Appliances	South	Jane	3

You want to find out how many times a salesperson named 'John' sold a product from the 'Electronics' category.

Here's how you would use the COUNTIFS function:

The formula would be: COUNTIFS(C1:C8,"John",A1:A8,"Electronics")

Let's break down the formula:

C1:C8: This is the first range of cells to be checked. It contains the names of the salespersons.
"John": This is the first criterion. The function will count any cell in the C2:C9 range that contains the text "John".
A1:A8: This is the second range of cells to be checked. It contains the product categories.
"Electronics": This is the second criterion. The function will count any cell in the A2:A9 range that contains the text "Electronics".

The COUNTIFS function will count a row only if both conditions are met. In this example, the rows that satisfy both criteria are:

Row 2: John sold an Electronics product.
Row 4: John sold an Electronics product.
Row 8: John sold an Electronics product.

The result of the formula will be 3.

Result of COUNTIFS(C1:C8, "John", A1:A8, "Electronics"):

3