COUNTIFS Function in Excel
- criteria_range1: (Where to look) The column or group of cells where Excel checks for your first condition. Required.
- criteria1: (What to look for) The value or condition you want Excel to find in criteria_range1. • It can be a number (50). • It can be text ("Apple"). • Or it can be a pattern ( see Common Criteria Patterns in COUNTIFS below ). Required.
- criteria_range2, criteria2: (Your second rule, and any extra rules) If you need to check more than one rule, you must add them in pairs. First tell Excel where to look next (criteria_range2), then tell it what to look for (criteria2). You can repeat this pattern up to 127 times. Optional.
What is the COUNTIFS function in Excel?
The COUNTIFS function counts the number of cells in a range that meet a single condition or multiple conditions.
Think of COUNTIFS like a security guard checking two IDs: a person is only counted if they are over 18 AND on the guest list.
Learn with Example: COUNTIFS
- Step 1: Understand the goal We want to count the invoices that belong to Acme Corp, but only if they are also Overdue. Because we need to check two rules at once, we will use COUNTIFS to look at both columns.
- Step 2: Start the formula Click the first empty cell in the Summary table, under Total Count, then type the function name and an open bracket. Type: =COUNTIFS(
- Step 3: Select criteria_range1 To select your criteria_range1, highlight the Client Name column in the Client Invoices table (B3:B7), then type a comma. Type: =COUNTIFS(B3:B7, criteria_range1 The first column of data you want Excel to search through.
- Step 4: Select criteria1 To select your criteria1, click the cell in the Overdue Invoices Summary table that says Acme Corp (F3), then type a comma. Type: =COUNTIFS(B3:B7, F3, criteria1 Exactly what you want Excel to find in that first column. It can be text, a number, or a cell reference.
- Step 5: Select criteria_range2 To select your criteria_range2, highlight the Payment Status column in the Client Invoices table (D3:D7), then type a comma. Type: =COUNTIFS(B3:B7, F3, D3:D7, criteria_range2 The second column of data you want Excel to search through.
- Step 6: Select criteria2 To select your criteria2, click the cell in the Overdue Invoices Summary table that says Overdue (G3), then type a closing bracket ). Type: =COUNTIFS(B3:B7, F3, D3:D7, G3) criteria2 Exactly what you want Excel to find in that second column. It can be text, a number, or a cell reference. Tip: for every way to write a condition, see the criteria and wildcards reference.
- Step 7: Check your first result Press Enter. COUNTIFS counts only the rows where the first rule and the second rule are both true. Two rows are both Acme Corp and Overdue (INV-103 and INV-105), so the result is 2. 2
- Step 8: Lock the criteria_ranges Copying the formula down now would break it. Double-click the cell, click inside B3:B7 and press F4 to lock it with $ signs. Do the same for D3:D7, then press Enter. The two criteria cells (F3 and G3) stay unlocked, so each copied row can read its own client and status. Type: =COUNTIFS($B$3:$B$7, F3, $D$3:$D$7, G3) Hint: The $ signs pin the ranges in place. Without them, the ranges slide down as you copy and miss rows.
- Step 9: Copy down the formula Now the ranges are locked, drag the small square at the bottom-right corner of the cell down the Summary table. Excel fills in the count for every other row automatically. Done Hint: Each copied row keeps the same locked ranges but reads its own rules from columns F and G.
You have a master list of client invoices. How do you create a quick summary showing how many invoices are currently marked as "Overdue" for each specific client?
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Client Invoices | Overdue Invoices Summary | ||||||
| 2 | Invoice ID | Client Name | Amount | Payment Status | Client to Check | Status to Check | Total Count | |
| 3 | INV-101 | Acme Corp | 5000 | Paid | Acme Corp | Overdue | ||
| 4 | INV-102 | Globex | 1200 | Overdue | Globex | Overdue | ||
| 5 | INV-103 | Acme Corp | 450 | Overdue | Initech | Paid | ||
| 6 | INV-104 | Initech | 8000 | Paid | ||||
| 7 | INV-105 | Acme Corp | 3200 | Overdue | ||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
When to use COUNTIFS
Use the COUNTIFS function when you need to count cells that meet multiple specific conditions at the same time.
- Counting between dates: Finding how many times an event happened during a specific time period, such as counting sales made between January 1st and January 31st.
- Checking multiple categories at once: Filtering a list by two different columns at the same time, such as finding employees who are in the "Sales" department and also have the title "Manager".
- Combining text and number rules: Mixing different types of rules together, such as counting inventory items marked as "In Stock" that also cost more than $50.
When not to use COUNTIFS
The COUNTIFS formula is incredibly useful, but it is not the right tool for these situations:
- When you only have one single rule: If you only need to check one column against one condition, such as counting how many times the word "Apple" appears, use the simpler COUNTIF function.
- When you just want to count non-empty cells: If your only goal is to count cells that have data in them, use the COUNTA function.
- When you need to add the actual numbers together: If you want to calculate the total dollar amount of sales instead of just counting how many sales were made, use the SUMIFS function.
COUNTIF vs COUNTIFS
The COUNTIFS function is the more powerful version of COUNTIF. If you are wondering which one to use, check this quick comparison table.
| Feature | COUNTIF | COUNTIFS |
|---|---|---|
| Best Used For | Counting with a single rule. | Counting with multiple rules. |
| Maximum Number of Rules | 1 | 127 |
| Can Use Wildcards | Yes (Limit: 1 rule) | Yes (Limit: 127 rules) |
| Can Compare Dates | Yes (Limit: 1 rule) | Yes (Limit: 127 rules) |
| Excel Version | 2007 and later | 2007 and later |
| Ease of Use | Beginner | Intermediate |
Bottom line: Use COUNTIF if you only have one rule to check. Use COUNTIFS if you need to check two or more rules at the same time.
Common Criteria Patterns in COUNTIFS
The most common rules you will use to count your data with COUNTIFS. Each rule is added as a pair: a range, then the condition to check in it.
| Criteria Pattern | Formula Example | Explanation |
|---|---|---|
| Exact Text Match | =COUNTIFS(A1:A10, "Apple", B1:B10, "Red") | Counts rows where column A is Apple and the same row in column B is Red. |
| Greater Than a Number | =COUNTIFS(A1:A10, ">50") | Counts cells with numbers greater than 50. The > symbol must be inside the quotation marks. |
| Between Two Dates | =COUNTIFS(A1:A10, ">=1/1/2026", A1:A10, "<=12/31/2026") | Counts dates in a range by checking the same date column twice: once for the start date and once for the end date. |
For every pattern, including more wildcard and cell-reference forms, see the full Excel Criteria and Wildcards reference.
Common Mistakes When Using COUNTIFS
Frequently Asked Questions about COUNTIFS
Quick answers about COUNTIFS
Does COUNTIFS ignore blank cells?
Yes. COUNTIFS automatically ignores completely empty cells when it applies your rules.
Can I use COUNTIFS to check for "OR" logic (this OR that)?
No. COUNTIFS requires every rule to be true at the same time. To count cells that are "Apple" OR "Orange", write two separate COUNTIFS formulas and add them together with a plus sign.
Does capitalization matter in the criteria?
No. COUNTIFS does not care about capital letters. Searching for "apple", "APPLE", or "Apple" gives the exact same count.
Can I count based on the cell color?
No. COUNTIFS only reads the data values inside a cell. It cannot count cells based on their background color or font formatting.
Why is my date criteria not finding any matches?
Excel can be strict about how dates are typed inside quotation marks. If your computer uses a Day/Month/Year format, typing ">12/31/2026" might fail. To be safe, use the DATE function inside your criteria, such as ">"&DATE(2026,12,31).
COUNTIFS Practice Workbook
Download ready-to-use Excel files and practice COUNTIFS hands-on
- All 5 worked examples (finance, sales, HR, ops, data)
- Blank Total Count cells to write each COUNTIFS yourself
- An answer key to check your counts