Excel practice › Excel functions › COUNTIFS

COUNTIFS in Excel: count the rows that meet every condition

COUNTIFS counts the rows that meet every condition you give it. With departments in B2:B9 and offices in C2:C9, the number of Sales people in Austin is =COUNTIFS(B2:B9,"Sales",C2:C9,"Austin").

Conditions come in pairs: a range, then what to look for in it. A row is counted only when all the pairs are true for it.

Skip to the 3 exercises ↓

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

criteria_range1
The first range to test.
criteria1
The condition for that range: "Sales", ">=5", a cell like F2, or an operator joined to a cell, ">="&F2.
criteria_range2, criteria2
More pairs, optional, up to 127 pairs in all. Each extra range needs the same number of rows and columns as criteria_range1. The ranges don't have to be next to each other.
Live examplePeople in the department in F2 and the office in G2
H2fx
ABCDEFGH
1EmployeeDeptOfficeYearsDeptOfficeCount
2A. RiveraSalesAustin2SalesAustin3
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5

How Excel reads it

  1. It takes the first cell of every range and checks each one against its own condition.
  2. If all of them pass, the count goes up by 1.
  3. It does the same for the second cells, the third, and so on down the ranges.

So the ranges are read side by side, row by row. That is why they all need the same size.

Between two numbers

A range like "from 3 to 6" is two conditions on the same column: at least 3, and at most 6. The column appears twice, once for each limit.

Live example3 to 6 years: the same range twice
F2fx
ABCDEF
1EmployeeDeptOfficeYears3 to 6 yrs
2A. RiveraSalesAustin24
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5

The mistake: AND when you meant OR

COUNTIFS counts a row only when all its conditions are true together. Ask for two different values of the same column and no row can ever pass. The result is 0, with no error to warn you:

Live example0: Sales and Finance at the same time
F2fx
ABCDEF
1EmployeeDeptOfficeYearsSales or Finance
2A. RiveraSalesAustin20
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5

Two more things to check when a count looks wrong. Every range needs the same number of rows and columns as the first one. And a cell reference never goes inside the quotes: ">="&F2 compares with the value of F2, while ">=F2" compares with the text F2 and counts nothing.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.

Exercise 1A department and a number

F2 holds a department. In G2, count the people in that department with 5 or more years at the company.

G2fx
ABCDEFG
1EmployeeDeptOfficeYearsDept5+ years
2A. RiveraSalesAustin2Sales
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5
Show the answer

=COUNTIFS(B2:B9,F2,D2:D9,">=5") gives 2.

Two pairs: the department equals F2, and the years are at least 5. Chen (6) and Miller (7) are the two Sales people who qualify.

Exercise 2Between two cells

F2 and G2 hold a minimum and a maximum number of years. In H2, count the people whose years are between the two, both included.

H2fx
ABCDEFGH
1EmployeeDeptOfficeYearsMinMaxCount
2A. RiveraSalesAustin225
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5
Show the answer

=COUNTIFS(D2:D9,">="&F2,D2:D9,"<="&G2) gives 4.

Column D is tested twice: at least F2, and at most G2. Each operator is in quotes and joined to its cell with &. Rivera (2), Okafor (4), Brooks (3) and Tanaka (5) are in range.

Exercise 3Three conditions, one of them a "not"

F2 holds an office and G2 a number of years. In H2, count the people in that office who are not in Sales and have more than G2 years.

H2fx
ABCDEFGH
1EmployeeDeptOfficeYearsOfficeOver yrsCount
2A. RiveraSalesAustin2Austin3
3B. ChenSalesDenver6
4C. OkaforFinanceAustin4
5D. MillerSalesAustin7
6E. NovakSupportDenver1
7F. HaddadFinanceDenver9
8G. BrooksSalesAustin3
9H. TanakaSupportAustin5
Show the answer

=COUNTIFS(C2:C9,F2,B2:B9,"<>Sales",D2:D9,">"&G2) gives 2.

<> means "not equal to", and it goes inside the quotes together with the text. Okafor (4 years) and Tanaka (5) are in Austin, outside Sales, with more than 3 years.

Questions people ask

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs and counts the rows that meet all of them. With a single pair they agree: =COUNTIFS(B2:B9,"Sales") and =COUNTIF(B2:B9,"Sales") both give 4 on the list above.

Does COUNTIFS use AND or OR?

AND. A row is counted only when every condition is true for it. For OR, add separate counts: =COUNTIFS(B2:B9,"Sales")+COUNTIFS(B2:B9,"Finance"). If a row could meet both conditions it would be counted twice, so this works cleanly when the two can't overlap, like two values of one column.

How do I count between two dates with COUNTIFS?

Use the date column twice: =COUNTIFS(A2:A9,">="&F2,A2:A9,"<="&G2), where F2 and G2 hold the first and the last date. The SUMIFS page has the same pattern running on a list of dated orders.

Why does COUNTIFS return 0?

Either no row meets all the conditions at once (the usual case is two values asked of the same column), or one condition never matches: a cell reference typed inside the quotes, or a text that differs from the data by a space. The COUNTIF page shows the space problem live.

Keep going

Lesson 5 is seven drills on a sales log: COUNTIF, SUMIF, SUMIFS, COUNTIFS and a conditional average, including a COUNTIFS with a text condition and a number condition together. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

COUNTIF function, with live examplesSUMIFS function, with live examplesAVERAGEIF and AVERAGEIFS functions, with live examplesAND, OR and NOT functions, with live examplesExcel interview questions for data analystsSUMIFS and COUNTIFS practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: COUNTIFS function, Microsoft Support: Use the COUNTIF function in Microsoft Excel. Last checked November 2026.