Yes. In Excel, use COUNTIF to count cells that match a number or a numeric condition: for example, with values in A2:A10, =COUNTIF(A2:A10,4) counts cells equal to 4. Use criteria such as greater than or equal to a number when you want to count a range of values rather than one exact match.
Count cells equal to a number
COUNTIF takes two arguments: the range to check and the criterion to match. For an exact numeric match, put the number directly in the formula without quotation marks. In the example below, A2:A10 contains 4, 7, 4, 0, -2, 7, 4, a blank cell, and 12.
With those values in A2:A10, enter =COUNTIF(A2:A10,4). The result is 3 because the value 4 appears three times. The blank cell does not change that count.
This is useful for questions such as how many orders have a quantity of 4, how many test scores equal 100, or how often a particular code appears in a column. The range can be a row, a column, or a rectangular block, although a single column or row is often easiest to review and maintain. If the formula is copied elsewhere, consider whether the range should remain fixed; an absolute reference such as $A$2:$A$10 will not shift when copied.
How numeric criteria work
For conditions involving a comparison operator, place the operator and number together inside quotation marks. Excel treats that combined text as the criterion. Common operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).
With the same values in A2:A10, =COUNTIF(A2:A10,">5") returns 3: the cells containing 7, 7, and 12 qualify. The formula =COUNTIF(A2:A10,">=0") returns 7 because it counts the nonnegative values 4, 7, 4, 0, 7, 4, and 12. Negative numbers and blanks do not meet that condition.
Keep the quotation marks around a comparison criterion. A bare number such as 4 is a valid exact-match criterion, but a comparison such as greater than 4 needs to be written as one criterion string, such as ">4". A frequent error is to put the operator outside the quotation marks or omit the quotation marks altogether.
Count values between two numbers
To count numbers within a range, combine a lower-bound and an upper-bound test with COUNTIFS. It applies multiple criteria and counts rows or cells that satisfy all of them. The boundaries can be inclusive or exclusive, depending on whether you use > or >=, and < or <=.
With the example values in A2:A10, the formula =COUNTIFS(A2:A10,">=1",A2:A10,"<=10") returns 5. It counts 4, 7, 4, 7, and 4; zero, -2, and 12 fall outside the inclusive range of 1 through 10. Both criteria use the same range because each individual cell must meet both boundaries.
For a strict range, such as values above 1 but below 10, change the operators to > and <. Decide whether the endpoints should be included before choosing operators. That small distinction matters in grade bands, price brackets, age limits, and other cutoffs where values exactly on a boundary may belong to one group or another.
Use a cell as the number to match
You do not have to type the criterion into the formula. Refer to a cell instead, which is useful when a user needs to change the target number without editing the formula. If C1 contains the number 4 and the data remains in A2:A10, =COUNTIF(A2:A10,C1) returns 3.
For a comparison using a value in a cell, join the operator and the cell reference with the ampersand. If C1 contains 5 and the values are in A2:A10, =COUNTIF(A2:A10,">"&C1) returns 3, counting the two 7s and the 12. The ampersand combines the operator text with the value from C1 to create the criterion.
This approach is convenient for dashboards and reports because the criterion can be stored in a clearly labeled input cell. If the formula will be copied, use an absolute reference for that input cell when appropriate, such as $C$1, so the reference does not move. The data range may also need dollar signs if every copied formula should inspect the same cells.
Numbers, numeric text, and the COUNT function
A cell that looks like a number is not necessarily stored as a numeric value. For example, data imported from another system may contain the text string “4” rather than the number 4. Formatting can make text and numbers look identical, but their underlying types can affect calculations, sorting, and criteria results. COUNTIF is designed to match criteria; it is not a dependable way to audit the underlying data type.
If the goal is to count cells containing numeric values, use COUNT. With the values in A2:A10 from the first example, =COUNT(A2:A10) returns 8, counting the eight numeric entries and excluding the blank. Unlike COUNTIF, COUNT does not test whether a number equals a specific value or meets a threshold.
To check one cell’s type, put =ISNUMBER(A2) in another cell when the value being checked is in A2. It returns TRUE for a number and FALSE for text, including text that looks numeric. If imported values need to behave as numbers, convert them using an appropriate method for the data, such as correcting the import or using Excel’s conversion options; changing the display format alone does not convert text into a number.
Zeros, blanks, dates, and decimal values
Zero is a number, so it can be counted as an exact value. To make the result unambiguous, suppose A2:A8 contains 4, 7, 0, -2, 7, 4, and 12, with no blank cells in that range. In that layout, =COUNTIF(A2:A8,0) returns 1. A blank cell and a cell containing zero are different things, even if a display format or formula makes them appear similar.
For the same range, =COUNTIF(A2:A8,"<>") counts nonempty cells and returns 7. When blanks or formulas returning empty text are important to the result, check a few representative cells: Excel distinguishes truly empty cells from cells containing formulas, and a visual blank does not always mean the cell is empty.
Excel stores dates and times as serial numbers, so numeric comparisons can apply to them too. For example, a date criterion can count dates on or after a chosen date. For a month, it is usually safer to count dates from the first day of that month up to, but not including, the first day of the next month. This avoids missing entries that include times on the final day. Make sure the cells contain real Excel dates, not date-looking text, and format the result cells as dates when you need to inspect the underlying values.
Exact decimal matching can be less useful than it first appears. Calculated decimal values may contain tiny floating-point differences, so a displayed value of 0.3 may not be stored as precisely the same value as a manually entered 0.3. If the intent is to count values within a tolerance, define upper and lower limits with two criteria rather than relying on an exact match.
Count numbers with another condition
Use COUNTIFS when a number must meet a condition and another field must also match. Each condition range should correspond to the same rows as the others. For example, suppose A2:A6 contains amounts 120, 80, 150, 200, and 100, while B2:B6 contains Paid, Paid, Pending, Paid, and Paid. The formula =COUNTIFS(A2:A6,">100",B2:B6,"Paid") returns 2: only the 120 and 200 entries are both above 100 and marked Paid.
When the question is “greater than one number or less than another,” you may need to add separate counts because a single COUNTIF criterion cannot express an either-or condition. With values in A2:A10, =COUNTIF(A2:A10,"<0")+COUNTIF(A2:A10,">10") returns 2 for the example data: -2 and 12 meet one of the two conditions. This addition is appropriate because those conditions do not overlap. If conditions can overlap, adding counts may count the same cell twice.
For several simultaneous conditions, COUNTIFS is generally clearer than combining many separate formulas. It is also important to keep the criteria ranges the same size. Mismatched ranges can produce errors or results that do not represent the rows you intended to compare.
Common COUNTIF problems and checks
- The result is zero unexpectedly: Check for extra spaces, text values that look numeric, and a criterion with missing or misplaced quotation marks. Confirm that the range includes the cells you expect.
- The count is too high: Look for duplicate values, hidden rows that are still included in the range, and criteria that are broader than intended. A criterion such as
">=5"includes 5;">5"does not. - The formula shows an error: Check that parentheses and quotation marks are paired and that the range and criterion are separated by a comma. Excel installations with a different regional list separator may display or require semicolons instead of commas.
- The formula does not update as expected: Confirm that calculation is set to automatic if results appear stale, and check whether the formula points to the intended cells. A fixed range does not automatically expand just because new data is typed below it.
In Microsoft 365 for Windows, formulas can be entered in a worksheet cell and confirmed with Enter. The same functions work in Excel for Mac and Excel for the web, although keyboard shortcuts and some interface details can differ. The formulas themselves do not need a special Windows-only version for the examples here.
Frequently Asked Questions
Can COUNTIF count a number greater than a value in another cell?
Yes. If the data is in A2:A10 and the comparison value is in C1, use =COUNTIF(A2:A10,">"&C1). The ampersand joins the greater-than operator to the value in C1.
How do I count numbers less than or equal to 10?
With the values to check in A2:A10, use =COUNTIF(A2:A10,"<=10"). The criterion includes 10 and all values below it; it does not include blank cells as numeric values in the intended comparison.
Does COUNTIF count cells that contain text instead of numbers?
It can match text criteria, and numeric-looking text can make results surprising because appearance and stored type are different. Use COUNT when you specifically need to count numeric values, or use ISNUMBER to inspect a particular cell.
What is the difference between COUNT and COUNTIF?COUNT counts numeric entries in a range without testing their values. COUNTIF counts entries that meet one criterion, such as being equal to 4 or greater than 5. Choose based on whether you need a type-based count or a condition-based count.
Can COUNTIF count values between two limits?
For two simultaneous limits, use COUNTIFS with the same range twice, once for the lower bound and once for the upper bound. For example, =COUNTIFS(A2:A10,">=1",A2:A10,"<=10") counts values from 1 through 10, including both endpoints.
Related
- Excel Drop-Down Lists: How to Create and Manage Them
- How to Insert a Checkbox in Excel: Steps and Tips
- Change Text to Numbers in Excel: Easy, Reliable Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
- Excel TEXTJOIN Limit: Character Cap and WorkaroundsIn Excel, TEXTJOIN can return a maximum of 32,767 characters in one cell—the worksheet cell-content limit. If its…
- Excel Named Range vs Table: How to Choose the Right ToolAn Excel named range gives a cell or formula a memorable name; an Excel table organizes related records…
- Excel Compare Two Sheets Cell by Cell: Formulas and ToolsYes. In Excel for Microsoft 365 on Windows, compare matching cells on two sheets with a formula such…
- Excel Filter Drop-Down Shortcut: Open a Column MenuIn Excel for Microsoft 365 on Windows, select a cell in a filtered column and press Alt+Down Arrow…
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.