To count numbers in an Excel range that meet a condition, use COUNTIF: for example, with values in B2:B20, =COUNTIF(B2:B20,">=10") counts values at least 10. For a number interval with both a lower and an upper limit, use COUNTIFS, which can apply both conditions to the same range.
How COUNTIF works with numbers
COUNTIF counts cells in one range that match one criterion. Its structure is COUNTIF(range, criteria): the range is the cells Excel checks, and the criterion describes what should count. The criterion can be an exact number, a comparison such as greater than or equal to a number, or a reference to a cell containing the value to check.
Suppose B2:B8 contains 5, 10, 12, 18, 20, 25, and one blank. With those values in B2:B8, =COUNTIF(B2:B8,">=18") returns 3, because 18, 20, and 25 meet the condition. The blank is not counted. The function returns a count, not the matching values themselves or their total.
For a direct exact-number test, a number can be used as the criterion without quotation marks. With the same values in B2:B8, =COUNTIF(B2:B8,12) returns 1. Use quotation marks around a criterion when it includes a comparison operator, as in ">=18"; otherwise Excel may interpret the operator and value incorrectly.
Count exact values and comparison thresholds
For a single threshold, the comparison operators are > (greater than), >= (greater than or equal to), < (less than), and <= (less than or equal to). A not-equal condition uses <>. The boundary matters: a condition of at least 10 includes 10, while greater than 10 does not.
With numbers in B2:B8, =COUNTIF(B2:B8,"<=12") returns 3 for the example list: 5, 10, and 12 are at or below 12. With that same range, =COUNTIF(B2:B8,"<>12") returns 5, counting the five numeric entries other than 12; the blank is not treated as a number equal to 12.
These patterns are useful for questions such as how many orders exceed a target, how many test scores fall below a pass mark, or how many inventory quantities are zero. Make the meaning of the boundary explicit in the criterion: “at least 10” means ">=10", whereas “more than 10” means ">10".
Count numbers between two limits
A number interval has two conditions, so use COUNTIFS rather than trying to put two comparisons into one COUNTIF criterion. COUNTIFS counts rows only when every supplied condition is satisfied. For an inclusive interval from 10 through 20, both endpoints are included.
With the values 5, 10, 12, 18, 20, 25, and a blank in B2:B8, =COUNTIFS(B2:B8,">=10",B2:B8,"<=20") returns 4: the qualifying values are 10, 12, 18, and 20. Notice that the same range is supplied twice, once for each boundary.
To exclude both endpoints and count only values strictly between 10 and 20, with the same values in B2:B8 use =COUNTIFS(B2:B8,">10",B2:B8,"<20"). The result is 2, for 12 and 18. Choosing inclusive or exclusive endpoints is a frequent source of off-by-one errors, especially when the limits represent grades, age bands, or price brackets.
For adjacent bands, decide which band owns each shared boundary. For example, “10 to less than 20” and “20 to less than 30” do not overlap: a value of exactly 20 belongs only to the second band. That approach avoids counting a boundary value twice when you summarize categories.
Use a cell reference for a changeable criterion
Typing a fixed threshold into a formula is fine for a one-off count. If you expect to change the threshold, place it in a worksheet cell and build the criterion from that cell. This makes the formula easier to reuse and lets someone adjust the input without editing the formula itself.
Suppose B2:B8 contains the example numbers and D2 contains the threshold 18. With the threshold in D2 and the values in B2:B8, =COUNTIF(B2:B8,">="&D2) returns 3. The ampersand joins the comparison text to the value in D2, creating a criterion such as greater than or equal to 18.
For a changeable interval, put the lower limit in D2 and the upper limit in E2. With numbers in B2:B8 and limits in D2 and E2, =COUNTIFS(B2:B8,">="&D2,B2:B8,"<="&E2) returns 4 when D2 is 10 and E2 is 20. If you copy a formula across a worksheet, consider whether the input references should stay fixed; dollar signs, as in $D$2, lock a reference when copying.
Number ranges, dates, and displayed values
Formatting changes how a value looks, not necessarily what value Excel stores. A cell formatted to show no decimal places may contain 12.4, for example. A criterion of exactly 12 will not count 12.4 just because the cell displays as 12 after rounding. If the intended rule is based on rounded values, create a helper column containing the rounded values or set criteria that reflect the actual stored values.
Dates are stored as serial numbers in Excel, so COUNTIF and COUNTIFS can count dates using comparison criteria. To count dates in a month, it is usually clearer to specify the first day of the month as an inclusive lower limit and the first day of the next month as an exclusive upper limit. This includes every time on the final day if the cells contain date-and-time values, too.
For example, if A2:A100 contains Excel dates and D2 contains the first day of a month while E2 contains the first day of the following month, with those cells laid out as described, =COUNTIFS(A2:A100,">="&D2,A2:A100,"<"&E2) counts dates in that month. Using a less-than next-month boundary avoids the common mistake of excluding times later than midnight on the final date.
Numbers imported from another file may be stored as text. They can look numeric but behave differently in calculations or comparisons. If a count seems too low, check a few cells by selecting them and inspecting the formula bar, and look for import warnings or inconsistent alignment. Converting the source data to real numbers may be more reliable than trying to accommodate mixed text and numeric entries in every counting formula.
COUNTIF compared with COUNT and COUNTIFS
Use COUNT when the question is simply how many cells contain numbers, regardless of their values. Use COUNTIF when one condition is needed, such as counting values equal to 0 or greater than 100. Use COUNTIFS when multiple conditions must be true, including a lower and upper limit on the same numeric range.
With the values 5, 10, 12, 18, 20, 25, and a blank in B2:B8, =COUNT(B2:B8) returns 6 because six cells contain numbers. It does not count the blank, and it does not apply a threshold. This distinction answers a common confusion: COUNTIF does not add numbers in a range; use a summing function for a total, and a counting function for the number of matching cells.
When criteria apply to different columns, COUNTIFS can count records that satisfy all conditions in corresponding rows. For instance, a sales table might count rows where the region is North and the amount is at least a target. Each criteria range should have the same shape and dimensions; using mismatched ranges can produce misleading results or an error.
Common COUNTIF problems and fixes
- Missing quotation marks around an operator: Write a comparison as a text criterion, such as
">=10". If the number is in a cell, join the operator text and cell reference with&. - Using COUNTIF for two bounds: A single
COUNTIFaccepts one criterion. UseCOUNTIFSwith the same numeric range twice to describe an interval. - Including the wrong endpoint: Decide whether each boundary is inclusive. Use
>=and<=to include endpoints; use>or<to exclude them. - Numbers stored as text: Imported values may not be numeric even when they look like numbers. Check and convert the source values when needed, rather than assuming display formatting proves their underlying type.
- Unexpected exact matches with decimals: Calculated decimal values can carry tiny precision differences that are hidden by formatting. If the rule is “within a tolerance,” use a lower and upper bound around the target instead of relying on exact equality.
- Counting a header: Start the range at the first data row, not the column heading. A heading normally will not meet a numeric test, but excluding it makes the formula clearer and prevents problems if the range later includes other text or labels.
One additional detail: COUNTIF is not case-sensitive for text criteria, and wildcards such as * and ? are for matching text patterns, not for defining numeric intervals. For ordinary numeric thresholds, use comparison operators rather than wildcard characters.
Frequently Asked Questions
How do I count numbers greater than a value in Excel?
Use COUNTIF with a greater-than criterion. With numbers in B2:B20, =COUNTIF(B2:B20,">10") counts values strictly greater than 10; use >=10 if 10 should count too.
How do I count numbers between two values in Excel?
Use COUNTIFS with a lower-bound and upper-bound condition on the same range. Use greater-than-or-equal and less-than-or-equal operators when both endpoints belong in the count.
Can COUNTIF count a range of numbers?
Yes, it can count numbers that meet one condition, such as greater than or equal to a limit. To define a bounded interval with both a minimum and maximum, use COUNTIFS because it supports multiple criteria.
Why does COUNTIF return zero for numbers that are visible?
Check that the values are stored as numbers rather than text, that the criterion uses the intended operator and boundary, and that the selected range contains the data rows. Also check whether displayed rounding hides a different underlying decimal value.
Does COUNTIF include the limit in a number range?
Only when the operator includes equality. For example, >= includes the lower limit and <= includes the upper limit; plain > and < exclude their respective limits.
Related
- Autofit Pictures in Excel: Fit Images to Cells
- Autofit Page in Excel: Fit a Worksheet to One Page
- Excel Autofit Shortcut Key: Resize Rows and Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEFT Function: Extract Text from the Left SideTo extract characters from the beginning of a cell in Excel, use the LEFT function. If A2 contains…
- Conditional Formatting Only If a Cell Has DataYes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New…
- Excel Formula to Uppercase the First Letter in CellsTo uppercase only the first character in an Excel cell while leaving the rest unchanged, use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)). If…
- Count Unique Records in Excel: Formulas and PivotTablesTo count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.