COUNTIFS counts the number of cells or rows that meet multiple criteria across different ranges. It returns a single number representing how many items satisfy all conditions at once.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)What does COUNTIFS do?
COUNTIFS is a statistical function that counts how many cells or rows meet several conditions at the same time. You provide pairs of ranges and criteria, and Excel checks each row against every pair. Only rows that satisfy all conditions are counted.
This function is essential for analysing data with multiple filters, such as sales by region and product, attendance by month and department, or inventory by category and status. It saves time compared to manual filtering and provides dynamic results that update automatically when data changes.
Imagine you manage a sales team and need to know how many deals over £5,000 were closed in the North region last quarter. Instead of filtering and counting manually, COUNTIFS gives you the answer instantly and updates as new data is added.
When to use COUNTIFS
- Counting sales transactions that meet both a minimum amount and a specific region.
- Tracking employee attendance by department and month.
- Monitoring inventory levels by product category and warehouse location.
- Counting students who scored above a threshold in multiple subjects.
- Identifying orders shipped within a date range and to a particular country.
COUNTIFS syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)| Argument | Required? | What to enter |
|---|---|---|
criteria_range1 | Required | The first range of cells to evaluate against the first criterion. Must contain the values you want to test. |
criteria1 | Required | The condition that cells in criteria_range1 must meet. Can be a number, text, cell reference, or expression like ">100". |
criteria_range2 | Optional | Additional ranges to test with corresponding criteria. Each range must have the same number of rows and columns as criteria_range1. |
criteria2 | Optional | The condition for criteria_range2. All criteria must be true for a row to be counted. |
What it returns: COUNTIFS returns a numeric value representing the count of rows or cells that meet all specified criteria.
COUNTIFS examples
1 Count sales in a specific region
We want to count how many sales occurred in the North region. The formula checks each cell in A2:A10 against "North" and counts matches. It returns 3 because rows 2, 5, and 8 have North in column A.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 3 | ||
| 3 | South | 800 | |||
| 4 | East | 1500 | |||
| 5 | North | 900 | |||
| 6 | West | 1100 | |||
| 7 | South | 1300 | |||
| 8 | North | 700 | |||
| 9 | East | 1600 | |||
| 10 | West | 1000 | |||
| 11 |
2 Count employees meeting performance and department criteria
We need to count employees in the Sales department who have a performance score of 4 or higher. The formula checks department equals "Sales" and score >=4. Only rows 2 and 5 meet both, returning 2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Department | Score | |||
| 2 | Sales | 4 | 2 | ||
| 3 | HR | 3 | |||
| 4 | Sales | 2 | |||
| 5 | Sales | 5 | |||
| 6 | IT | 4 | |||
| 7 | HR | 5 | |||
| 8 | Sales | 3 | |||
| 9 | IT | 4 | |||
| 10 | Sales | 1 | |||
| 11 |
3 Count inventory items by category and status
We want to count how many items in the Electronics category have the status "In Stock". The formula checks category equals "Electronics" and status equals "In Stock". Rows 2 and 4 match, so it returns 2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Status | |||
| 2 | Electronics | In Stock | 3 | ||
| 3 | Clothing | Out of Stock | |||
| 4 | Electronics | In Stock | |||
| 5 | Home | In Stock | |||
| 6 | Electronics | Out of Stock | |||
| 7 | Clothing | In Stock | |||
| 8 | Home | Out of Stock | |||
| 9 | Electronics | In Stock | |||
| 10 | Clothing | In Stock | |||
| 11 |
Common COUNTIFS errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | Criteria ranges have different sizes (e.g., one range has 10 rows, another has 9). | Ensure all criteria ranges have the same number of rows and columns. |
| Incorrect count (too low) | Text criteria are not enclosed in double quotes, or wildcards are used incorrectly. | Wrap text criteria in double quotes, e.g., "North". Use * for wildcards. |
| Incorrect count (zero) | Date criteria are entered as text without proper comparison operators. | Use comparison operators with dates, e.g., ">=2024-01-01", and ensure dates are stored as dates. |
| #NAME? | Function name misspelled, e.g., COUNTIF instead of COUNTIFS. | Check spelling: COUNTIFS with an S at the end. |
Tips for using COUNTIFS
- Use cell references for criteria to make formulas dynamic, e.g., =COUNTIFS(A2:A10, D1).
- Combine COUNTIFS with SUMIFS or AVERAGEIFS for deeper analysis.
- Use wildcards (* and ?) in text criteria for partial matches.
- Remember that COUNTIFS is case-insensitive for text.
- Test criteria separately with COUNTIF to debug complex COUNTIFS formulas.
Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet one condition, while COUNTIFS counts cells that meet multiple conditions across different ranges.
Can COUNTIFS count dates between two values?
Yes, use two criteria on the same range: >=start_date and <=end_date.
Does COUNTIFS work with wildcards?
Yes, you can use * (any number of characters) and ? (single character) in text criteria.
Can I use COUNTIFS across multiple sheets?
Yes, but each criteria range must be on the same sheet or use 3D references carefully. Typically, ranges are on one sheet.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. COUNTIFS reference