Use Excel’s COUNTIFS function to count rows that meet two or more conditions: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). It applies an AND test, so a row is counted only when it meets every supplied criterion.
For example, with departments in A2:A10 and order statuses in B2:B10, count rows where the department is Sales and the status is Open with =COUNTIFS(A2:A10,"Sales",B2:B10,"Open"). The examples below explain how to adapt that pattern for numbers, dates, alternatives, and text searches.
What COUNTIFS does
COUNTIFS counts records across one or more corresponding ranges. It is useful for questions such as “How many open orders belong to Sales?”, “How many invoices are at least $100 and still unpaid?” or “How many entries fall within this date period?” Unlike a filter, it returns a number in a cell and updates when the underlying data changes.
The function has been available in Excel since Excel 2007 and is included in Microsoft 365 for Windows. Its arguments come in pairs: a range to inspect, followed by the condition to apply to that range. Add another range-and-condition pair for each additional requirement. Excel supports up to 127 such pairs.
In the Sales example, Excel checks each position in A2:A10 and B2:B10 together. A row contributes one to the result only if its department cell is Sales and its status cell is Open. If either condition fails, that row does not count.
How to build a multiple-criteria formula
Start with one pair, then add a pair for every other condition. All criteria ranges should cover corresponding records and have the same dimensions. If one range begins on row 2 and another begins on row 3, Excel may return an error or evaluate unintended records.
- Identify the records to count and the column for each condition.
- Write the first criteria range and its criterion.
- Add each additional range and criterion in matching pairs.
- Check that every range starts and ends on the same rows.
Assume a small order list has these values: row 2 is Sales/Open, row 3 Sales/Closed, row 4 Support/Open, row 5 Sales/Open, row 6 Support/Closed, row 7 Sales/Open, row 8 Sales/Pending, row 9 Support/Open, and row 10 Sales/Closed. With departments in A2:A10 and statuses in B2:B10, use =COUNTIFS(A2:A10,"Sales",B2:B10,"Open"). The result is 3: rows 2, 5, and 7 meet both requirements.
For criteria that may change, put the desired values in worksheet cells instead of typing them into the formula. With departments in A2:A10, statuses in B2:B10, the chosen department in G2, and the chosen status in H2, use =COUNTIFS(A2:A10,G2,B2:B10,H2). If G2 contains Sales and H2 contains Open, the result is 3. This approach makes a summary or dashboard easier to reuse.
AND criteria versus OR criteria
The central rule is that separate criteria pairs in one COUNTIFS formula are joined with AND. For example, adding a minimum amount means the row must match the department, status, and amount requirement.
With departments in A2:A10, statuses in B2:B10, and order amounts in C2:C10, count Sales orders that are Open and at least 100 with =COUNTIFS(A2:A10,"Sales",B2:B10,"Open",C2:C10,">=100"). The result is 2: the Open Sales orders in rows 2 and 5 have amounts 120 and 150. Row 7 is Open and Sales, but its amount is 70.
OR means that a row can satisfy either of multiple alternatives. A common approach is to add separate counts. With departments in A2:A10 and statuses in B2:B10, count Sales rows whose status is Open or Pending with =COUNTIFS(A2:A10,"Sales",B2:B10,"Open")+COUNTIFS(A2:A10,"Sales",B2:B10,"Pending"). The result is 4: three Open Sales orders and one Pending Sales order.
Adding separate counts is safest when the alternatives cannot overlap, as with the distinct status values Open and Pending. If conditions overlap, the same row could be counted twice. For OR conditions on a single column, use a method that avoids duplicate counting, such as counting distinct categories or creating a helper column, depending on the data and the question. Do not assume that adding criteria pairs creates OR logic; it creates AND logic.
Numbers, operators, and criteria cells
Criteria can compare values using operators such as >, >=, <, <=, <> (not equal to), and =. Put an operator and a fixed value together in a quoted criterion, such as ">=100". A numeric criterion like 100 can also be written without quotation marks when no operator is needed.
When the comparison value is in a cell, join the operator to the cell reference with &. With amounts in C2:C10 and a threshold in G2, count amounts greater than or equal to that threshold with =COUNTIFS(C2:C10,">="&G2). If G2 contains 100, the result for the example amounts is 4: 120, 150, 110, and 200 meet the threshold. If you include other conditions, add their range-and-criterion pairs to the same formula.
This construction is a frequent source of errors: ">=G2" searches for text containing those characters; it does not use the value in G2. Use ">="&G2 to make a comparison against the cell’s value.
Counting dates in a period
Excel stores dates as serial numbers, so date comparisons work well when the cells contain real Excel dates rather than text that merely looks like a date. To count dates in a month, use a lower bound for the first day and an exclusive upper bound for the first day of the next month. This includes every time on the final day if the data contains date-and-time values.
Assume dates are in D2:D6, with the values January 5, January 20, February 3, February 25, and March 2, 2026. Count dates in January with =COUNTIFS(D2:D6,">="&DATE(2026,1,1),D2:D6,"<"&DATE(2026,2,1)). The result is 2. The first criterion includes January 1; the second excludes February 1 and everything after it.
The DATE function avoids ambiguity between regional date formats. If the start and end dates are in cells, the same operator-and-cell pattern works. Make sure the cells contain actual dates and not imported text; changing the number format alone does not convert text into date values.
Text criteria and wildcards
Text criteria can match a complete value, such as "Sales", or use wildcards for partial matches. The asterisk (*) represents any number of characters, while the question mark (?) represents exactly one character. A tilde (~) tells Excel to treat a wildcard character as a literal character.
For example, assume product names are in E2:E20 and categories are in F2:F20. To count product names that begin with “Pro” and belong to the Accessories category, use =COUNTIFS(E2:E20,"Pro*",F2:F20,"Accessories"). The result depends on the contents of those ranges. The wildcard is useful for names such as “Pro Case” or “Protective Cover,” but it can also match unintended entries, so inspect what the pattern includes.
For a criterion stored in a cell, add the wildcard to the cell reference. With product names in E2:E20 and a prefix in G2, use =COUNTIFS(E2:E20,G2&"*"). If G2 contains Pro, it counts names beginning with Pro. COUNTIFS text comparisons are not case-sensitive. If case differences matter, a different approach is needed, such as a helper column using a case-sensitive function.
COUNTIFS compared with COUNTIF and other approaches
COUNTIF handles one range and one criterion. Choose it for a simple count, such as counting one status. Choose COUNTIFS when the count must satisfy more than one condition; it also works for a single pair, so a formula can be expanded later without changing function names.
For more complex logic, a helper column can make the rule visible and easier to audit. For example, it can label each row as qualifying or not qualifying, after which a simple count can summarize the labels. This can be helpful when conditions involve complicated text transformations or when users need to understand why a row qualifies. For ordinary AND criteria, COUNTIFS is usually shorter and easier to maintain.
Filters are another option when a person wants to inspect matching records rather than calculate a reusable count. A PivotTable can summarize larger datasets by categories and is useful for interactive reporting, but it requires a different setup. The best choice depends on whether the goal is a single formula result, a browsable subset, or a broader summary.
Common errors and troubleshooting
- Ranges have different sizes. Keep the criteria ranges aligned, such as A2:A100 and B2:B100, rather than mixing whole columns with partial ranges unnecessarily.
- AND was mistaken for OR. Every pair in one COUNTIFS formula must match. Add separate counts for mutually exclusive alternatives or choose a method designed for overlapping conditions.
- An operator was treated as text. When a criterion uses a value from a cell, concatenate it as in
">="&G2. - Dates are stored as text. Imported date strings may not compare as real dates. Convert the source values to Excel dates before relying on date criteria.
- Unexpected spaces or spelling differences appear. “Open” and “Open ” may not behave as intended. Clean source data or use a carefully chosen wildcard if variable text is expected.
- Absolute references are needed when copying. When copying a formula down, use references such as
$A$2:$A$100to keep a criteria range fixed. Relative references are useful when the intended range should move.
COUNTIFS does not distinguish uppercase from lowercase text. It also treats an empty criterion differently from a blank-looking cell in some situations, especially if formulas return an empty string. If a result seems wrong, test each condition on its own, verify the source values, and then add the pairs back one at a time.
Frequently Asked Questions
Can COUNTIFS use multiple criteria in the same column?
Yes. You can repeat a range in separate pairs, but those criteria are combined with AND. For example, asking the same cell to equal both Open and Pending cannot match a single value. To count either value, add separate counts or use another OR-based method.
How many criteria can COUNTIFS handle?
Excel supports up to 127 range-and-criteria pairs in one COUNTIFS function. In practice, very long formulas can be difficult to review, so consider helper columns or a summary design when the logic becomes hard to maintain.
Is COUNTIFS case-sensitive?
No. Standard COUNTIFS text matching ignores letter case, so “sales” and “Sales” are treated as equivalent. Use a different, case-sensitive method if the distinction is important.
Can COUNTIFS count dates between two dates?
Yes. Use one condition for dates greater than or equal to the start and another for dates less than the day after the end, or use an exclusive next-period boundary. Confirm that the source cells contain Excel date values.
Why does COUNTIFS return zero when matching text?
Check for extra spaces, spelling differences, a criterion referencing the wrong cell, or a range that does not align with the other ranges. Also remember that text comparisons are not case-sensitive, so capitalization alone is not usually the cause.
Related
- Class Attendance Sheet Template Excel: Setup and Tips
- Choose a Date in Excel: Calendar Options and Steps
- Checklist in Excel: How to Create, Track, and Reuse One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
- Excel COUNTIF Contains Text: Formula & ExamplesTo count cells that contain a specific text string anywhere within them, use =COUNTIF(range,"*"&text&"*") or simply =COUNTIF(range,"*text*") when…
- Excel Day Timeline: Build a Daily Schedule or Date FilterAn Excel day timeline can mean either a daily schedule laid out across worksheet columns or a PivotTable…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- COUNTIF Equals Text in Excel: Examples and TipsTo count cells that equal a particular text value, use COUNTIF with the range and the text in…
- VLOOKUP in Excel: Purpose, Examples, and Limits ExplainedVLOOKUP in Excel looks for a value in the first column of a range and returns a related…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.