In Excel, COUNTIF counts cells in a range that meet one condition. For example, with status labels in A2:A10, =COUNTIF(A2:A10,"Paid") counts cells whose contents are Paid; the result is the number of matching cells, not a sum of any values beside them.
What COUNTIF Does
COUNTIF is useful when you need a quick count based on one rule: how many orders are pending, how many scores exceed a threshold, or how many entries contain a particular word. It returns a number and does not change the cells it checks. The range can contain text, numbers, dates, or blanks, and the condition can be typed into the formula or taken from another cell.
The function has two inputs: the range to inspect and the criterion that defines a match. In the example above, A2:A10 is the range and Paid is the criterion. COUNTIF checks each cell in that range and increases the count when a cell meets the condition. It does not distinguish uppercase from lowercase, so Paid and PAID match the same text criterion.
COUNTIF works in Microsoft Excel for Microsoft 365 on Windows, as well as in many earlier Excel versions. It is a standard worksheet function, so you can enter it in a cell like other formulas and use the usual fill handle to copy it. The examples below use comma argument separators, which are standard in the stated Windows setup. Some regional settings use semicolons instead; if Excel rejects a comma-separated formula, check the list separator configured for your system.
Counting Text Values
For an exact text match, put the text in straight double quotation marks. Suppose A2:A8 contains Open, Closed, Open, Pending, Open, Closed, and Open. With those statuses in A2:A8, =COUNTIF(A2:A8,"Open") returns 4 because Open appears four times. The formula does not require the text to be sorted or adjacent.
If the criterion is already written in a worksheet cell, refer to that cell instead of typing the word into the formula. Assume A2:A8 contains the status labels above and D2 contains Open; =COUNTIF(A2:A8,D2) returns 4. This makes the count easy to change: replace the value in D2, and the formula recalculates for the new criterion.
For a partial text match, use an asterisk as a wildcard. An asterisk represents any number of characters, including no characters. If A2:A8 contains North, Northeast, South, North Zone, and other region labels, and three entries contain the word North, then with those labels in A2:A8, =COUNTIF(A2:A8,"*North*") returns 3. The asterisks allow characters before or after North. A question mark wildcard represents exactly one character, so it can match a one-character variation in a known pattern.
Wildcards have a special meaning. If you want to search for a literal asterisk or question mark rather than use it as a wildcard, place a tilde before it in the criterion. For example, with product codes in A2:A20 and a literal asterisk somewhere in the code, =COUNTIF(A2:A20,"~*") counts cells equal to an asterisk. For more complex text rules, such as case-sensitive matching, COUNTIF alone is not the right tool.
Counting Numbers and Applying Comparisons
To count values above, below, or equal to a threshold, include the comparison operator in quotation marks. Suppose B2:B10 contains 45, 120, 100, 85, 210, 99, 100, 75, and 150. With those values in B2:B10, =COUNTIF(B2:B10,">=100") returns 5: the qualifying values are 120, 100, 210, 100, and 150. The comparison checks the stored numeric values, not just how they look after formatting.
To make the threshold adjustable, join the operator to a cell reference with the ampersand. Assume B2:B10 contains the same numbers and D2 contains 100; =COUNTIF(B2:B10,">="&D2) returns 5. The ampersand combines the text operator with D2’s value to create the criterion. If D2 changes to 120, the count updates to 3.
Useful comparison operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). If you need to count values within a range, use two conditions with COUNTIFS, or subtract one count from another carefully. A single COUNTIF criterion cannot express both a lower and upper boundary at once.
Counting Dates and Blanks
Excel stores dates as serial numbers and displays them using date formatting. This means COUNTIF can compare dates, but the criterion must be built in a way Excel can interpret. Suppose C2:C20 contains valid Excel dates, with four dates on or after January 1, 2026. With those dates in C2:C20, =COUNTIF(C2:C20,">="&DATE(2026,1,1)) returns 4. DATE constructs a date value for the comparison, avoiding ambiguity between regional date formats.
If the dates include times, a criterion for a date may not include every timestamp on that date when used as an exact match. For instance, a cell containing January 1, 2026 at 9:00 a.m. is not numerically identical to midnight at the start of January 1. For a whole-day range, use a lower bound for the day and an upper bound for the next day, generally with COUNTIFS.
COUNTIF can also count cells that are blank or not blank. Assume A2:A10 contains three genuinely empty cells and seven cells with entries; with this range, =COUNTIF(A2:A10,"") returns 3. With the same contents, =COUNTIF(A2:A10,"<>") returns 7. A formula that displays an empty string can behave like a blank for this kind of criterion, so consider how cells were created if the count surprises you.
Using COUNTIFS for More Than One Condition
COUNTIF handles one condition. When a count must satisfy two or more conditions at the same time, use COUNTIFS, which pairs each range with a criterion. For example, suppose A2:A10 contains regions, B2:B10 contains order statuses, and the data has three rows where the region is West and the status is Paid. With those aligned data ranges, =COUNTIFS(A2:A10,"West",B2:B10,"Paid") returns 3.
All criteria in COUNTIFS must be true for a row to count. The criteria ranges should generally be the same size and correspond row by row; mismatched ranges can produce errors or results that do not represent the intended records. COUNTIFS is not an “either condition” function: if you want West or East, you can add two COUNTIF results, or design a different formula depending on the conditions and data.
When you find yourself repeating COUNTIF for multiple categories, a PivotTable may be more useful. A PivotTable can summarize counts by one or more fields without writing a separate formula for every category. COUNTIF remains convenient for a compact dashboard, a single result, or a count that needs to update in a particular cell.
Common COUNTIF Problems and Fixes
A result of zero does not always mean there are no apparent matches. The cells may contain leading or trailing spaces, nonbreaking spaces copied from a web page, or a slightly different spelling. Check a few source values directly, and consider cleaning imported text before counting. COUNTIF ignores case differences but does not ignore extra spaces or other characters.
Other frequent causes of unexpected results include:
- Numbers stored as text: A value that looks like 100 may be stored as text. Check the cell format and how the data was imported; converting the source values to numbers may be necessary.
- Wrong range: Confirm that the range includes all intended rows and does not include headings or unrelated totals.
- Missing quotation marks: Text criteria and comparison expressions must be quoted as shown in the examples. A cell reference used as the criterion is not quoted.
- Unintended wildcards: An asterisk in the criterion broadens the match. Use a tilde when the symbol should be treated literally.
- Dates stored as text: A date-looking string may not be a real Excel date value. Convert it to a valid date before relying on numeric date comparisons.
If Excel displays a formula rather than a result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change a text-formatted formula cell to General, then edit and re-enter the formula. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent is Control+`. If a formula shows an error after entry, also confirm the argument separator expected by your regional settings.
COUNTIF Compared with Related Options
Choose COUNTIF when the question is “How many cells in this range match this one rule?” Choose COUNTIFS when several conditions must all be met. Use SUMIF or SUMIFS instead when the goal is to add numeric values that meet one or multiple conditions, not simply count the qualifying cells. These functions may look similar, but they answer different questions.
For example, “How many orders are Paid?” calls for COUNTIF. “How much revenue came from Paid orders?” calls for SUMIF if the status range and revenue range are aligned. If a reader needs a flexible summary across many categories, a PivotTable may be easier to maintain than a long set of separate formulas. The choice depends on whether the required output is a count, a sum, or a broader grouped report.
For a case-sensitive count, COUNTIF is insufficient because its text matching is not case-sensitive. A formula using SUMPRODUCT and EXACT can be used for that specialized need, but it is more complex and should be built around the actual data layout. If case differences matter to the result, verify the method with representative examples before relying on it.
Frequently Asked Questions
What is the basic COUNTIF formula in Excel?
It uses a range to inspect and one criterion to match. For example, with categories in A2:A20, a formula such as =COUNTIF(A2:A20,"Travel") counts cells equal to Travel. The criterion can also be a comparison, wildcard pattern, or cell reference.
Can COUNTIF count cells that contain a word?
Yes. Use asterisks around the word for a partial match. With descriptions in A2:A20, =COUNTIF(A2:A20,"*urgent*") counts cells containing urgent anywhere in the text, regardless of capitalization.
How do I count cells that are not blank?
Use the not-equal-to operator with an empty criterion. With entries in A2:A20, =COUNTIF(A2:A20,"<>") counts nonblank cells, including cells whose contents may have been produced by formulas, depending on their displayed result.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive, so text such as Paid and PAID is treated as a match for the same criterion. Use a case-sensitive approach based on EXACT if capitalization is part of the rule.
Why does COUNTIF return zero when the value is visible?
Check for extra spaces, text-versus-number mismatches, dates stored as text, and a range that misses the cell. Also verify the criterion spelling and wildcard placement. The displayed appearance of a cell does not always reveal its underlying value or hidden characters.
Practical Checks Before Relying on a Count
Before using a COUNTIF result in a report, test the rule against a small sample of rows you can inspect manually. Confirm that the criterion reflects the intended meaning: an exact label, a threshold, a partial phrase, or a blank. For a regularly updated list, use a range that will include new records, or consider an Excel Table so formulas can refer to structured columns rather than fixed row limits. Finally, compare the formula result with a known small subset. This simple check helps catch criteria that are technically valid but do not answer the question you intended to ask.
Related
- Clean Up an Excel Spreadsheet: A Practical Guide
- Clean an Excel Spreadsheet: Steps for Cleaner Data
- Clean Excel File Online: Safe Steps and Best Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX and MATCH: Find Values in Large WorksheetsIn Excel, INDEX and MATCH work together to find a value in one range and return the corresponding…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Oracle LEFT Function: How to Get Text from the LeftOracle SQL does not provide a built-in LEFT() function. To return the first characters of a string, use…
- VLOOKUP Between Two Sheets: Examples and Common FixesTo use VLOOKUP across two Excel sheets, put the lookup value on one sheet and the data table…
- C++ Function Syntax: A Complete GuideThe basic syntax for a function in C++ is return_type function_name(parameter_list) { body }. For example, int add(int…
- Excel Sort Entire Row by One Column Without Splitting DataYes. To sort entire rows by one column in Excel for Microsoft 365 on Windows, select the full…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.