Yes. In Excel, use COUNTIFS with the same range repeated for each condition; the conditions are combined with AND. For example, with numbers in B2:B10, =COUNTIFS(B2:B10,">=10",B2:B10,"<=20") counts values from 10 through 20, inclusive.
How COUNTIFS handles repeated ranges
COUNTIFS counts rows that meet every criteria-range and criteria pair supplied to it. Its syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each range is paired with one condition, and the pairs work together as AND tests. There is no restriction against using the same range more than once: Excel evaluates each condition against that range.
For a numeric range, repeating the range is useful for defining a boundary. With numbers in B2:B10, this formula returns 5 if the cells contain 5, 10, 12, 20, 21, 15, 8, 18, and 25:
With the listed values in B2:B10, the formula counts numbers at least 10 and no greater than 20; the result is 5.
=COUNTIFS(B2:B10,">=10",B2:B10,"<=20")
The two tests apply to each cell in turn. A value must pass both, so 10 and 20 qualify, while 8 and 21 do not. This pattern works for other ranges too, such as dates, scores, or quantities.
When same-column criteria mean AND
Repeated criteria ranges are most straightforward when they describe a single interval or a set of simultaneous requirements. Common examples include values between two limits, dates within a period, or text that meets two compatible wildcard conditions. If the requirements cannot both be true of one value, the result will be zero.
- For numbers from 1 through 100, use a lower-bound test and an upper-bound test.
- For dates in January, count dates on or after January 1 and before February 1.
- For text, use multiple tests only when a single cell can satisfy both patterns.
In a worksheet with dates in E2:E6 and the actual Excel dates January 1, 2026 and February 1, 2026 in G1 and G2, respectively, this formula counts dates in January. If E2:E6 contains January 3, January 15, January 31, February 1, and January 20, the result is 4.
=COUNTIFS(E2:E6,">="&G1,E2:E6,"<"&G2)
The upper limit is exclusive, which avoids accidentally including February 1. Using a next-period boundary is particularly helpful when dates may include times: a date-time on January 31 is still earlier than February 1, whereas a test for less than or equal to January 31 might exclude times later that day.
When you want OR instead
A frequent source of confusion is whether multiple criteria mean “and” or “or.” Repeating a range in one COUNTIFS formula means AND. If you need to count cells that match one condition or another, add separate counts instead. For example, with the values North, South, North, East, North, South, West, North, and East in A2:A10, the formula below returns 6: four cells equal North and two equal South.
With those nine region names in A2:A10, the two exact-match counts are added to return 6.
=COUNTIF(A2:A10,"North")+COUNTIF(A2:A10,"South")
For two simple alternatives on one range, COUNTIF is enough for each part. For alternatives that each have additional conditions on other columns, add separate COUNTIFS formulas, one for each alternative. Check whether the groups overlap: adding counts can count the same row twice if it meets both alternatives. Distinct exact text values such as North and South cannot both describe the same cell, but broad wildcard patterns can overlap.
For instance, a cell containing “Alaska” could match both a pattern beginning with A and a pattern ending with a. Adding those wildcard counts would count that cell twice. If a unique count across overlapping groups is important, design criteria that do not overlap or use a more tailored formula or helper column.
Writing criteria correctly
Criteria may be text, a number, a cell reference, or a comparison expression. Text criteria such as "North" are written in quotation marks in a formula. Operators such as greater than or equal to must also be inside quotation marks. When the comparison value comes from a cell, join the operator and reference with the concatenation operator, ampersand.
With scores in B2:B10 and the lower and upper limits stored in D1 and D2, this formula includes both limits. If D1 is 10 and D2 is 20, it counts values from 10 through 20.
=COUNTIFS(B2:B10,">="&D1,B2:B10,"<="&D2)
Do not put the whole expression inside quotes when it refers to a cell. For example, ">=D1" is treated as text, not as “greater than or equal to the value in D1.” The concatenated form ">="&D1 tells Excel to use the operator and the value in the cell.
COUNTIFS also supports wildcards in text criteria: an asterisk matches any number of characters, and a question mark matches one character. A tilde escapes a wildcard when you need to search for a literal asterisk or question mark. COUNTIFS text matching is not case-sensitive, so “north” and “North” are treated as equivalent for an ordinary text criterion.
COUNTIFS compared with COUNTIF
COUNTIF accepts one range and one condition, while COUNTIFS can evaluate multiple range-condition pairs. For one test, such as counting cells equal to “North,” COUNTIF is shorter and clear. For a range test on one column, COUNTIFS lets you apply both bounds without creating a separate calculation.
Although the function name suggests that several different columns are required, they are not. You can repeat one range, use different ranges, or combine both approaches. For example, a formula could test that a score is within a band and that a corresponding status column equals a particular label. When ranges refer to different columns, they should cover corresponding rows and have the same dimensions. Using matching ranges such as A2:A100 and B2:B100 makes it clear that the tests refer to the same records.
Common problems and how to fix them
If a formula returns zero or a count that seems wrong, check the criteria and source data before changing the function. Several small details can affect the result:
- Incompatible conditions: A cell cannot be both equal to 5 and equal to 10. Repeated criteria are AND tests, so impossible combinations correctly return zero.
- Text that looks numeric: A value stored as text may not behave like a true number in a numeric comparison. Check whether imported values need conversion.
- Uneven range sizes: Keep all criteria ranges aligned to the same rows and dimensions. Misaligned ranges can make a count misleading or cause an error.
- Spaces in text: A trailing space in a cell can make an exact text match fail. Inspect the source value or clean the data when appropriate.
- Dates stored as text: Excel comparisons work reliably when the cells contain real date values, not strings that merely look like dates. Verify the underlying data if date criteria do not match.
- Quotation marks and operators: Put literal text and comparison expressions in straight double quotes. Join a comparison operator to a cell reference with
&.
In Microsoft 365 for Windows, enter the formula in a cell and press Enter. Excel for Mac and Excel for the web use the same function and formula structure; ribbon locations and keyboard shortcuts can differ, but no special version-specific syntax is needed for these examples. If Excel displays a formula error after pasting one, check that the quotes are straight quotes and that the list separator matches the regional settings of your Excel installation.
Choosing the right pattern
Start by translating the question into logic. If a row must satisfy every requirement, use one COUNTIFS formula and include a pair for each requirement. If the same field needs to fall between two boundaries, repeat that field’s range. If a row can qualify by either of two alternatives, add separate counts, while considering whether the groups overlap.
It can help to write the condition in plain language before entering a formula. “Count scores from 10 to 20” means score is at least 10 AND score is at most 20. “Count North or South” means region equals North OR region equals South. This distinction prevents the common mistake of putting incompatible OR choices into repeated criteria pairs and expecting Excel to treat them as alternatives.
For maintainable workbooks, put changing thresholds or category names in worksheet cells and refer to those cells in the formula. That makes the criteria visible and easier to update than editing quoted values inside a long formula. Use clearly labeled input cells and keep the data range consistent as rows are added; structured Excel table references can also help formulas expand with table data.
Frequently Asked Questions
Can I use the same range twice in COUNTIFS?
Yes. Give the range once for each condition. Excel applies every condition, so the cell must meet them all.
How do I count values between two numbers?
Repeat the numeric range with a lower-bound and upper-bound criterion, such as greater than or equal to the minimum and less than or equal to the maximum. That includes both endpoints.
Does COUNTIFS use AND or OR?
Criteria pairs in a single COUNTIFS formula use AND. For OR alternatives, add separate counts, and check for overlap so a row is not counted more than once.
Can COUNTIFS count dates in a month?
Yes. Use a start date with a greater-than-or-equal-to test and the first day of the next month with a less-than test. Ensure the source cells contain real Excel dates.
Is COUNTIFS case-sensitive?
No. Ordinary text comparisons in COUNTIF and COUNTIFS are not case-sensitive. If capitalization must distinguish matches, a different approach is needed.
Why does my same-column formula return zero?
Confirm that all criteria can be true at once, then check the range alignment, number-versus-text storage, extra spaces, and whether date cells contain actual dates. The AND behavior is often the key issue.
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
- Excel Right-Click Shortcut: Open the Menu by KeyboardIn Excel for Microsoft 365 on Windows, press Shift+F10 to open the context menu—the menu you normally see…
- Excel Formula Not Calculating? Common Causes and FixesIf a formula will not calculate in Excel, check that the cell is not formatted as Text, that…
- How to Sort and Filter a Table in Excel Step by StepTo sort and filter an Excel table, select a cell in it and use the arrow in a…
- Excel Filter Shortcut: How to Turn Filters On and OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. Select a cell…
- Excel Not Equal Symbol: How to Use <> in FormulasIn Microsoft Excel, the not-equal operator is <>—a less-than sign followed by a greater-than sign. Use it in…
- Excel SUBSTITUTE Double Quote: Formulas and ExamplesTo remove straight double quotation marks from a cell in Excel, use =SUBSTITUTE(A2,CHAR(34),"") when the text is in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.