Yes. Excel’s COUNTIFS can apply more than one condition to the same range by repeating that range for each condition: =COUNTIFS(A2:A10,">=10",A2:A10,"<=20"). The conditions are combined with AND, so this example counts cells in A2:A10 whose values are both at least 10 and no more than 20.
How COUNTIFS handles repeated ranges
COUNTIFS counts rows or cells that satisfy every criteria-range and criteria pair supplied to it. Its general syntax is COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...). Each range is paired with a condition, and Excel tests the pairs together. A range can be repeated; it does not have to be a different column for each condition.
For example, repeating a numeric range lets you set a lower and upper limit. Repeating a text range with two incompatible exact values does not mean “either value”: a single cell cannot equal both “Open” and “Hold” at once, so that setup ordinarily returns zero. This AND behavior is the key to choosing the right formula.
In a typical worksheet, each row represents one record. If several criteria are applied to the same range, Excel checks whether the cell in that row meets all of them. Criteria applied to different ranges are also ANDed. Thus, a count of records in a particular region and within a date interval requires both the region and date tests to pass for the same row.
Count values within a range
Two conditions on one numeric range are useful for counting values between limits, including both endpoints. Suppose A2:A10 contains these values: 8, 12, 15, 20, 21, 12, a blank, 9, and 18. The values from 10 through 20 are 12, 15, 20, 12, and 18, so the result should be 5.
With those values in A2:A10, enter this formula in any cell outside the data:
=COUNTIFS(A2:A10,">=10",A2:A10,"<=20")
In the formula bar, the second criterion is typed with the ordinary less-than character, as "<=20" appears here as escaped HTML text; it evaluates as “less than or equal to 20.” Excel returns 5 for the sample values. Criteria such as >, >=, <, <=, and <> can be used for greater than, at least, less than, at most, and not equal to tests.
When a limit is stored in a cell rather than typed into the formula, join the comparison operator to the cell reference. With values in A2:A10, a minimum in D1, and a maximum in E1, use:
=COUNTIFS(A2:A10,">="&D1,A2:A10,"<="&E1)
This approach makes the bounds easy to change without editing the formula. Ensure that the limit cells contain numeric values, not numbers stored as text, if the source range contains numbers.
OR criteria in the same range
To count cells matching one value OR another, add separate counts rather than putting conflicting exact-match criteria into one COUNTIFS call. Imagine A2:A8 contains Open, Hold, Closed, Open, Pending, Hold, and Closed. There are two Open cells and two Hold cells, for a combined total of 4.
With those statuses in A2:A8, use:
=COUNTIF(A2:A8,"Open")+COUNTIF(A2:A8,"Hold")
COUNTIF is sufficient here because each count has just one range and one condition. You can also use separate COUNTIFS calls if each alternative needs additional AND conditions. For instance, if another column contains dates and you want Open-or-Hold records only within a date interval, calculate the Open count for that interval and add the Hold count for the same interval.
For a short list of alternatives, explicit addition is easy to read and works in widely used desktop versions of Excel. A compact array-criteria formula may also work in current Microsoft 365, but explicit addition makes the OR logic visible and avoids uncertainty when sharing a workbook with people using older Excel versions. Be careful with overlapping alternatives: adding a count for “greater than 10” to a count for “greater than 20” counts values over 20 twice.
Combine criteria across columns and dates
Repeated ranges are not limited to numeric boundaries. You can pair one range with multiple conditions and add other ranges for conditions on separate fields. Consider a table with region in A2:A20, order date in B2:B20, and status in C2:C20. To count East-region orders dated during 2025 that are not Cancelled, set each criterion against the relevant column.
With the stated columns and rows, use:
=COUNTIFS(A2:A20,"East",B2:B20,">="&DATE(2025,1,1),B2:B20,"<"&DATE(2026,1,1),C2:C20,"<>Cancelled")
This counts a row only when all four tests are true: region is East, the date is on or after January 1, 2025, the date is before January 1, 2026, and the status is not Cancelled. The exclusive upper bound is a reliable way to include every date in the year, even if the date cells contain times. If they are plain dates with no times, a less-than-or-equal-to December 31 criterion can also be used.
Excel stores real dates as serial numbers, so date criteria work best when the cells contain valid Excel dates. Using DATE avoids ambiguity about regional date formats such as month/day versus day/month. Check that all ranges cover the same rows: mixing A2:A20 with B2:B21 can cause COUNTIFS to return a range-size error.
Use cell references, text, and wildcards
Criteria can be literal text, numbers, comparison expressions, cell references, or wildcard patterns. When building a criterion from a cell, use concatenation for operators. When matching text, remember that ordinary exact text matching in COUNTIFS is not case-sensitive: “east” and “East” are treated as equivalent for this purpose.
*stands for any sequence of characters, including no characters. A criterion such as"North*"matches text beginning with North.?stands for one character. A criterion such as"A?"matches two-character text beginning with A.~escapes a wildcard when you need to match it literally. For example,"~*"searches for a literal asterisk.
For example, suppose A2:A20 contains product descriptions, and you need to count entries beginning with “Cable” while B2:B20 contains quantities of at least 5. With those ranges, use:
=COUNTIFS(A2:A20,"Cable*",B2:B20,">=5")
The wildcard is useful when descriptions include extra words or model details. It is not a substitute for cleaning inconsistent data: leading spaces, spelling variations, and unexpected punctuation can still make records behave differently than intended.
Blank cells and criteria pitfalls
Blank-related counts can be confusing because a cell that looks empty may contain a formula returning an empty string, a space, or another invisible character. A criterion of "" is used to count blank-looking cells, while "<>" is commonly used to count nonblank cells. If the result seems off, inspect the source cells and test whether they contain formulas or spaces; visual appearance alone does not prove that a cell is truly empty.
Other frequent causes of unexpected results include:
- Using AND when you meant OR: Two exact-match criteria against the same cells must both be true. Add separate counts for alternatives.
- Putting operators inside an unjoined reference: To compare against a value in D1, use a criterion such as
">="&D1, not a text string that merely contains the characters D1. - Using inconsistent range sizes: Every criteria range in one
COUNTIFSformula must have the same dimensions. - Confusing text and numbers: A number stored as text may not behave like a numeric value in comparisons. Check the data type and convert the source when needed.
- Including the header row by accident: Start all criteria ranges on the same first data row, or a header can be included in the count.
Also note that COUNTIFS does not distinguish capitalization in its ordinary text criteria. If a task truly requires case-sensitive counting, use a different approach, such as a formula built around EXACT and SUMPRODUCT; that is a separate requirement from applying multiple conditions to one range.
COUNTIFS versus COUNTIF and other approaches
Use COUNTIFS when a result depends on multiple conditions that must all be satisfied, whether those conditions use one repeated range or several columns. Use COUNTIF for a single range-and-condition test. For OR alternatives, adding individual COUNTIF results is often the clearest method; for OR alternatives that also have shared conditions, add multiple COUNTIFS results.
For example, if you are counting either Open or Hold records in region East, adding two COUNTIFS formulas expresses the logic directly: one count for East plus Open, and another for East plus Hold. This avoids accidentally asking one cell to satisfy both status values. If the list of alternatives is long or changes frequently, consider putting the allowed values in a small helper list and using a more advanced formula suited to the Excel versions used by the workbook’s audience.
A PivotTable is another practical alternative when you want to summarize many categories, inspect subtotals, or let users filter the data interactively. It may be more convenient than maintaining a long formula, but it is not necessary for a simple count. For a reusable worksheet calculation that updates when source data changes, COUNTIFS is usually straightforward.
Build and check the formula
When composing a multi-condition count, first state the rule in plain language. Decide which tests are AND conditions and which are OR alternatives. Then map each AND test to a range-and-criteria pair, repeating a range when it needs more than one test.
- Identify the data rows and make every criteria range cover the same rows.
- Translate each rule into a criterion, such as an exact label, a comparison, or a wildcard pattern.
- Repeat the same range for multiple simultaneous tests on that field.
- Add separate counts for mutually exclusive OR choices, then compare the result with a small manual sample.
For troubleshooting, temporarily simplify the formula: test one criterion, then add the next pair. This helps reveal whether the issue is a range reference, a text mismatch, a date value, or misunderstood AND/OR logic. Keep criteria in cells when users need to adjust thresholds regularly, and label those inputs so the purpose of each one is obvious.
Frequently Asked Questions
Can COUNTIFS use the same range more than once?
Yes. Repeat the range with a different criterion for each condition. This is especially useful for setting lower and upper bounds, and all of those conditions must be true for a cell or row to count.
How do I count one value or another in the same column?
Add separate counts, such as one COUNTIF for each exact value. Two different exact-match criteria in a single COUNTIFS call are ANDed, not treated as alternatives.
Can I use two criteria on the same range to count between dates?
Yes. Repeat the date range with a start-date criterion and an end-date criterion. For date-time values, use the day after the final date as an exclusive upper limit so times during the last day are included.
Why does my COUNTIFS formula return zero?
Check whether you accidentally combined alternatives as AND conditions, whether the text matches the source, and whether numbers or dates are stored as text. Also confirm that the criteria ranges are aligned and equal in size.
Is COUNTIFS case-sensitive?
No. Its standard text criteria are not case-sensitive. If capitalization must be treated as meaningful, use a separate case-sensitive method rather than relying on ordinary COUNTIFS text matching.
Related
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
- Contacts List Template Excel: Build a Useful Workbook
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Add a Formula in Excel to Calculate DatesTo calculate a date in Excel, enter a starting date in one cell and use a formula such…
- Excel Formula If Cell Contains Value: Practical ExamplesTo return one result when a cell is not empty and another when it is empty, use =IF(A2<>"","Yes","No").…
- Excel SEQUENCE: Create Automatic Number SequencesYes. In Microsoft Excel for Microsoft 365, enter a SEQUENCE formula to generate a series automatically: with an…
- Excel Formula to Remove Duplicates From One ColumnIn Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(A2:A100) in an empty cell to return a live…
- VLOOKUP Excel: Extended Uses, Examples, and LimitsVLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate…
- XLOOKUP Excel Version: Compatibility and AvailabilityXLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.