To count numbers within an inclusive range in Excel, use COUNTIFS with a lower and an upper limit: with the values 4, 7, 10, 12, 15, 18, 20, 22, and 25 in A2:A10, enter =COUNTIFS(A2:A10,">=10",A2:A10,"<=20"). The result is 5. If by a range of values you mean a list of separate items rather than an interval, COUNTIF can count those too, but the formula is different.
What does a range of values mean?
The phrase can describe two common counting tasks. An interval asks how many cells fall between two boundaries, such as numbers from 10 through 20. A list asks how many cells match any of several specified values, such as Red or Blue. Sorting out which meaning you need is important: COUNTIF uses one criterion at a time, while COUNTIFS can combine criteria and an array of COUNTIF results can total matches from a list.
In Excel for Microsoft 365 on Windows, COUNTIF has this structure: =COUNTIF(range,criteria). The range is the cells Excel checks, and the criterion tells Excel what qualifies. COUNTIFS uses pairs of ranges and criteria, allowing several conditions to be checked together. These functions are available in Excel for Mac and Excel for the web as well; the basic syntax is the same.
Count numbers inside an interval
For an interval with both endpoints included, COUNTIFS is usually the clearest choice. Give it the same range twice, once for the minimum and once for the maximum. With numbers 4, 7, 10, 12, 15, 18, 20, 22, and 25 in A2:A10, the formula =COUNTIFS(A2:A10,">=10",A2:A10,"<=20") returns 5: the qualifying values are 10, 12, 15, 18, and 20. The greater-than-or-equal and less-than-or-equal operators include the endpoints.
If the boundaries are stored in cells, join each comparison operator to the cell reference with an ampersand. With the same numbers in A2:A10, a minimum of 10 in D2, and a maximum of 20 in D3, =COUNTIFS(A2:A10,">="&D2,A2:A10,"<="&D3) also returns 5. This is convenient when a user needs to change the boundaries without editing the formula.
For an exclusive interval, change the operators. To count values greater than 10 and less than 20, use =COUNTIFS(A2:A10,">10",A2:A10,"<20"); with the example values, it returns 3 for 12, 15, and 18. Check whether a question means inclusive or exclusive boundaries before choosing the signs.
Use COUNTIF for one-sided conditions
COUNTIF works well when the condition has only one boundary. With the example numbers in A2:A10, =COUNTIF(A2:A10,">=10") returns 7, because it counts 10, 12, 15, 18, 20, 22, and 25. To count values below 10 in that range, =COUNTIF(A2:A10,"<10") returns 2.
COUNTIF cannot directly express both a minimum and maximum in one criterion. A subtraction method can work: count everything at or above the minimum, then subtract everything above the maximum. With the example numbers in A2:A10, =COUNTIF(A2:A10,">=10")-COUNTIF(A2:A10,">20") returns 5. This is valid, but COUNTIFS is generally easier to read and maintain because it states both boundaries explicitly.
Count a list of separate values
Suppose a column contains categories and you want to count rows whose category is any item in a short list. In Microsoft 365, COUNTIF can evaluate a criteria range as an array, and SUM can add the resulting counts. If A2:A10 contains Red, Blue, Red, Green, Blue, Red, Yellow, Green, Blue, and D2:D3 contains Red and Blue, =SUM(COUNTIF(A2:A10,D2:D3)) returns 6: three Red cells plus three Blue cells.
This method counts matches for each entry in the criteria list and adds them. It is useful when the list may change, because you can add or replace criteria in cells instead of building a long formula. Keep the criteria list limited to the cells you intend to use; including blank cells can affect what gets counted.
One important catch: repeated criteria are counted repeatedly. If D2:D4 contains Red, Blue, Red, the same example formula will return 9, not 6, because the three Red matches are added twice. Remove duplicate criteria if you want each matching data cell counted only once. Alternatively, write separate COUNTIF calls and add them, which is straightforward for a small, fixed set of values but cumbersome for a long list.
Combine conditions with COUNTIFS
Use COUNTIFS when a row must meet multiple conditions at the same time. For example, suppose A2:A6 contains East, West, East, East, West, while B2:B6 contains 120, 80, 160, 90, 150. To count East rows with a value of at least 100, enter =COUNTIFS(A2:A6,"East",B2:B6,">=100"). The result is 2, for the first and third rows.
All conditions in COUNTIFS are combined as AND: each counted row must satisfy every criterion. That differs from counting any one of several categories, which is an OR task. For OR across categories in the same column, sum the individual COUNTIF results or use the criteria-list approach above. When using COUNTIFS across columns, make sure each criteria range has the same dimensions; mismatched ranges can produce an error or an unintended result.
Write criteria for text, dates, and wildcards
For an exact text match, put the text in quotation marks in the formula, or refer to a cell containing the text. COUNTIF is not case-sensitive, so Red and red are treated as matches. If you need to distinguish upper- and lowercase letters, COUNTIF alone is not the right tool.
Wildcards help when only part of a text value is known. An asterisk represents any number of characters, while a question mark represents one character. A tilde escapes a wildcard when you want to search for the literal character. If C2:C7 contains Red pen, Pen case, pencil, blue pen, marker, and pen, =COUNTIF(C2:C7,"*pen*") returns 5. The asterisks allow characters before or after the letters pen, and the match is not case-sensitive.
Dates are stored as serial values in Excel, even though cells are usually displayed in a date format. For a date interval, use COUNTIFS with a start and end date, preferably held in cells and joined to the comparison operators. This avoids relying on how a typed date is interpreted under different regional settings. Make sure the source cells contain real Excel dates rather than text that merely looks like a date.
Blanks, zeros, and common counting mistakes
Blank cells and zero are not the same thing. A criterion of 0 counts cells whose value is zero; a criterion of an empty string, written as "" inside the formula, is used to count empty-looking cells. Cells containing a formula that returns an empty string can also behave like blanks for this purpose. If results seem surprising, inspect the underlying cells, not only their displayed appearance.
- Use quotation marks around comparison criteria such as
">=10"; the operator and number form one text criterion. - When a boundary is in a cell, concatenate the operator and reference, as in
">="&D2. Writing the operator as text without joining the cell value will not create a dynamic boundary. - Check that numbers are actually numeric. Values imported as text may not behave like numbers in a numeric comparison.
- Confirm that the counted range excludes headers and includes all intended data rows.
- Remember that COUNTIF is case-insensitive and that wildcard characters can match more than an exact text value.
Excel uses commas between formula arguments in the examples here. Depending on Windows, Mac, or Excel for the web regional settings, your installation may expect semicolons instead. If Excel rejects a correctly structured formula, check the list separator setting for your locale.
Choose between COUNTIF, COUNTIFS, and alternatives
Use COUNTIF for one condition, such as a single category, a threshold, or a text pattern. Use COUNTIFS for multiple conditions that must all be true, including a numeric interval in one column or conditions across multiple columns. Use SUM with COUNTIF when you need to total matches for several alternatives. These choices make the logic easier for another person to understand and reduce the chance of overlooking a boundary.
For a modest fixed list, adding separate COUNTIF results is easy to audit. For a list that changes often, keep criteria in worksheet cells and sum the results. If the question is not simply how many cells qualify—for example, if you need to return matching rows or calculate a total for those rows—use a different approach suited to that task, such as filtering the data or using SUMIFS for a conditional sum. Counting, extracting, and summing are different operations even when they use the same criteria.
Frequently Asked Questions
Can COUNTIF count values between two numbers?
Not with a single COUNTIF criterion. Use COUNTIFS with the same range for the lower and upper limits, such as criteria of greater than or equal to the minimum and less than or equal to the maximum.
How do I count any of several values in Excel?
Put the values in a criteria list and, in Microsoft 365, use SUM around COUNTIF with that list. Check the list for duplicates, because repeated criteria add the same matching cells more than once.
Should I use COUNTIF or COUNTIFS?
Use COUNTIF for one criterion. Use COUNTIFS when every counted cell or row must meet two or more conditions, such as being in a specified interval and belonging to a particular category.
Why does my COUNTIF formula return zero?
Check the range, spelling, comparison operator, and whether numeric values are stored as numbers rather than text. Also verify that a cell-based criterion is joined to its operator with an ampersand and that the boundary cell contains the expected value.
Is COUNTIF case-sensitive?
No. COUNTIF does not distinguish uppercase from lowercase text. It also interprets asterisks and question marks as wildcards, so use a tilde before one when you need to match the wildcard character itself.
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 Formula for Matching Values: Exact Match GuideFor an exact match in a list, use =MATCH(E2,A2:A100,0): it returns the matching item’s position within the range,…
- COUNTIF Formula for Attendance in ExcelTo count attendance marks in Excel, use =COUNTIF(B2:U2,"P"). If a student’s 20 session entries are in B2:U2 and…
- Combine HLOOKUP and VLOOKUP in ExcelYes. You can nest VLOOKUP inside HLOOKUP so VLOOKUP finds the row number and HLOOKUP finds the column…
- Excel Filter Based on Multiple Criteria: How It WorksTo filter Excel data by multiple criteria, use Data > Filter and set a condition in each relevant…
- Excel Formula for Start of Month: Dates, Examples & TipsTo return the first day of the month for a date in cell A2, use =DATE(YEAR(A2),MONTH(A2),1). If A2…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.