Yes. Excel’s COUNTIFS function counts rows or cells that meet multiple criteria at the same time. For example, with regions in A2:A100 and order statuses in B2:B100, =COUNTIFS(A2:A100,"East",B2:B100,"Paid") counts records where the region is East and the status is Paid.
What COUNTIFS does
COUNTIFS is Excel’s built-in function for counting cells that satisfy two or more conditions. It is useful when a single condition is not enough: you might need the number of paid orders from one region, employees in a department with a particular start date, or products that meet both a category and a stock threshold.
The function checks criteria in pairs. Each pair consists of a range to inspect and the condition to apply to that range. A row is counted only when it passes every pair of tests. This is an AND relationship: all specified criteria must be true for a record to count.
For example, a sales report might contain region, representative, status, amount, and order date in separate columns. You can count orders for one region handled by one representative, or orders that are paid and above a chosen amount, without adding a helper column. COUNTIFS is available in current Microsoft 365 Excel and has been included in Excel since Excel 2007.
Syntax and matching range sizes
The general syntax is =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...). The first range and criterion are required; additional range-and-criterion pairs are optional. Excel supports up to 127 pairs, though a formula with that many conditions can be difficult to maintain.
Each criteria range must have the same number of rows and columns as the others. Usually, each range refers to a column covering the same records, such as A2:A100 and B2:B100. If one range starts on row 2 and another starts on row 3, Excel may still calculate the formula, but the results can be misleading because the conditions no longer refer to corresponding records. Keep the ranges aligned and, where possible, use identical dimensions.
With region names in A2:A8 and order statuses in C2:C8, =COUNTIFS(A2:A8,"East",C2:C8,"Paid") counts rows where both conditions hold. If the sample data contains four East orders marked Paid, the result is 4. The criteria can be typed directly into the formula, or can refer to cells so that users can change the selection without editing the formula.
Combine criteria with AND
To require several conditions simultaneously, add another range and criterion pair. For instance, with region in A2:A8, representative in B2:B8, and status in C2:C8, the formula below counts East-region orders assigned to Maya that are Paid:
With those fields in A2:A8, B2:B8, and C2:C8 respectively, enter =COUNTIFS(A2:A8,"East",B2:B8,"Maya",C2:C8,"Paid"). In a dataset with three East orders assigned to Maya, of which two are Paid, the result is 2.
Criteria can also test numbers. Suppose amounts are in D2:D8 and you want East orders with an amount of at least 100. With region in A2:A8 and amounts in D2:D8, use =COUNTIFS(A2:A8,"East",D2:D8,">=100"). If four East orders in the example have amounts of 100 or more, the result is 4. The comparison operator and value are written together as the criterion.
For criteria stored in worksheet cells, join the operator to the cell reference. If F1 contains 100, and region and amount are in A2:A8 and D2:D8, use =COUNTIFS(A2:A8,"East",D2:D8,">="&F1). The ampersand combines the operator text with the value in F1. This makes it easy to adjust a threshold without changing the formula.
Count alternatives with OR logic
A common point of confusion is that separate pairs in COUNTIFS mean AND, not OR. A row must satisfy every pair. If you want to count rows that match one condition or another in the same field, add separate COUNTIFS calculations. For example, to count East orders whose status is either Paid or Pending, count each status and add the results.
With regions in A2:A8 and statuses in C2:C8, use =COUNTIFS(A2:A8,"East",C2:C8,"Paid")+COUNTIFS(A2:A8,"East",C2:C8,"Pending"). In the sample data, four East orders are Paid and one is Pending, so the result is 5. This addition is safe here because a status cell cannot be both Paid and Pending. If the alternatives can overlap, the same record may be counted twice; avoid that by adjusting the logic or subtracting duplicates.
For a small set of alternatives, adding multiple COUNTIFS calculations is clear and straightforward. If the list of choices is long or changes frequently, consider using a helper column, a PivotTable, or a more advanced formula approach rather than building an unwieldy expression. The right method depends on whether the result must update interactively and how easy it should be for someone else to audit.
Use operators, text criteria, and wildcards
COUNTIFS accepts exact text, numbers, and comparison criteria. Common comparison operators are >, >=, <, <=, <> for “not equal to,” and =. Text criteria are generally not case-sensitive, so criteria such as “east” and “East” match the same text in ordinary use.
Use wildcards when a text value only needs to match a pattern. An asterisk (*) matches any number of characters, including none; a question mark (?) matches one character. With customer names in E2:E100, the criterion "North*" matches text beginning with North, while "????" matches text with four characters. A tilde (~) escapes a wildcard when you need to match a literal asterisk or question mark.
To count cells that are not equal to a text value, use a not-equal criterion such as "<>Closed". For more complex tests, such as “contains this word but does not end in a particular suffix,” check the pattern carefully against sample values. Wildcards are pattern matching, not regular expressions, and COUNTIFS does not interpret every character as a special pattern operator.
Handle dates and date ranges
Excel stores valid dates as serial numbers, so date criteria work best when the cells contain real Excel dates rather than text that merely looks like a date. To avoid ambiguity from regional date formats, build a date criterion with the DATE function or refer to a cell containing a valid date.
With order dates in E2:E8, count dates in February 2026 by applying a lower bound and an exclusive upper bound. Use =COUNTIFS(E2:E8,">="&DATE(2026,2,1),E2:E8,"<"&DATE(2026,3,1)). If three records fall from February 1 through February 28, the result is 3. The exclusive March 1 boundary also works if the cells contain date-and-time values, because times on February 28 remain earlier than March 1.
For a rolling date window, use cells containing the start and end dates, joining the comparison operators to those cells. Decide whether the end date should be inclusive or exclusive, especially when timestamps are present. An inclusive criterion of “less than or equal to” an end date can exclude times later in that day if the end-date cell represents midnight; an exclusive next-day boundary is often easier to reason about.
Common mistakes and ways to check a result
- Using OR when the formula means AND: Every criteria pair in one COUNTIFS must be true. Add separate counts for alternatives when the intended logic is OR.
- Misaligned ranges: Make sure every range covers the same rows and represents the same records. A header row included in only one range can shift the intended comparisons.
- Leaving operators outside the criterion: For a numeric threshold, write a criterion such as
">=100", or join the operator to a cell reference. A comparison written as plain text will not behave as intended. - Dates stored as text: Text dates may not compare as chronological dates. Convert them to genuine Excel dates before relying on date boundaries.
- Unexpected spaces or spelling differences: “Paid” and “Paid ” are different text values. Check the source data for trailing spaces, inconsistent labels, and blanks.
- Hard-to-maintain formulas: Put changing criteria in clearly labeled cells and use consistent ranges. This makes the calculation easier to review and update.
When a result looks wrong, first test each condition on its own with a separate COUNTIF calculation, then add the conditions back one at a time. Compare the result to a filtered view of the source rows. This helps distinguish a formula-logic error from inconsistent or incorrectly typed source data.
COUNTIFS compared with related tools
Use COUNTIF when there is only one condition; it has a simpler range-and-criterion structure. Use COUNTIFS for two or more conditions that must all be met. If you need to add values rather than count matching records, SUMIFS is the closer counterpart: it sums a designated range subject to one or more criteria.
A PivotTable may be more convenient when you want to summarize many categories, compare several fields, or let readers explore results interactively. COUNTIFS is often better for a specific result in a report cell that should update as criteria change. A filtered table is useful for inspecting the actual matching records, while COUNTIFS returns a number without displaying those records.
For simple multi-condition counts, COUNTIFS is usually the most direct choice. For complex logic combining nested alternatives, calculations across arrays, or data that needs reshaping, other formulas or tools may be more suitable. Choose based on clarity and maintainability, not just on whether a single formula can be made to work.
Frequently Asked Questions
Can COUNTIFS use more than two criteria?
Yes. Add a range-and-criterion pair for each additional condition. All pairs must be satisfied for a row to count, and Excel supports up to 127 such pairs.
Does COUNTIFS count rows that meet either condition?
Not when the conditions are entered as separate pairs in the same function: those pairs use AND logic. To count either of two alternatives, add separate COUNTIFS results, and check whether overlapping matches could be counted twice.
Can I use a cell reference as the COUNTIFS criterion?
Yes. For an exact match, use a cell reference as the criterion argument. For a comparison such as greater than or equal to the value in F1, join the operator and reference with &, as in ">="&F1.
Are COUNTIFS criteria case-sensitive?
No. COUNTIFS normally treats uppercase and lowercase versions of text as equivalent. If a task requires case-sensitive matching, COUNTIFS alone is not the appropriate case-sensitive test.
Why does my COUNTIFS formula return zero?
Check that the criteria actually match the data, that all ranges are aligned, and that dates or numbers are stored as values of the right type. Also look for extra spaces, misspellings, and criteria operators that were entered as plain text instead of being joined correctly to a value.
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 Text Filter Wildcards: How to Use ThemIn Excel for Microsoft 365 on Windows, text-filter wildcards let you match patterns: use * for any number…
- Calculate Weekday from Date in Excel: Formulas and TipsTo calculate a weekday from a date in Excel, use =WEEKDAY(A2,2) if the date is in A2; Monday…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- IFERROR With IF in Excel: Syntax, Examples, and TipsTo use IFERROR with IF in Excel, put the IF function inside IFERROR: =IFERROR(IF(B2=0,"No quantity",A2/B2),"Check values"). IF makes…
- Excel Attendance Percentage: Formula and ExamplesTo calculate attendance percentage in Excel, divide the number of sessions marked present by the number of recorded…
- Rank in Excel Ignoring Blanks: Formula and ExamplesYes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.