In Excel, =COUNTIF(B:B,I2) counts the cells in column B that match the value or counting criterion in cell I2. For example, if I2 contains the name Mira, the formula returns how many cells in column B contain Mira.
What the formula means
COUNTIF has two arguments: the range to inspect and the criterion to match. In this formula, B:B means the entire B column, while I2 is a reference to the cell holding the criterion. Because I2 is a reference rather than text typed directly into the formula, you can change the value in I2 and the count will update automatically.
The match is based on the cell contents, not on formatting such as font color, fill color, or number format. COUNTIF is also not case-sensitive: Mira and MIRA are treated as the same text for counting. For ordinary text or numbers, it looks for a match to the criterion; special criteria such as wildcards or comparison operators can change what counts as a match.
The result is a single number. It is not a list of the matching rows, and it does not change or highlight the cells in column B. If you need to see which records match, you can use Excel’s filtering tools or a separate formula designed to return matching rows.
Enter the formula in Excel
In Microsoft Excel for Microsoft 365 on Windows, select the cell where you want the count to appear, type the formula, and press Enter. The formula can go anywhere outside the data and criteria cells; for example, you might put it in J2. The formula has the same syntax in Excel for Mac and Excel for the web.
- Put the value to look for in I2, such as Mira.
- Select the cell where you want the answer.
- Enter the formula, using commas between arguments in a standard English-language Excel setup.
- Press Enter. Change I2 to another value to recalculate the count.
If your Excel installation uses a regional list separator, it may require semicolons rather than commas. That is a regional settings difference, not a different COUNTIF method. If Excel displays the formula as text instead of calculating it, check that the cell is not formatted as Text, then re-enter the formula.
Worked example with names
Suppose B1 is a heading called Owner, B2:B7 contain Mira, Omar, Mira, Lina, Mira, and Omar, and I2 contains Mira. The layout has six data entries in column B and the search value in I2. With that layout, enter =COUNTIF(B:B,I2) in the result cell. The answer is 3 because Mira appears three times in B2:B7.
Using a full-column range also includes B1, but the heading Owner does not match Mira and so does not affect this result. If you instead use the data-only range, the count is the same for this example. With the data in B2:B7 and the criterion in I2, the bounded-range version is =COUNTIF(B2:B7,I2). This version makes the intended data area explicit and excludes the heading.
A useful test is to change I2 from Mira to Omar. The result becomes 2. If you change it to Lina, the result becomes 1. This is why a cell reference is convenient for a summary or small search panel: one formula can answer different questions as the selected criterion changes.
Using a whole-column range
B:B tells Excel to consider every row in column B, including the heading and currently empty cells. A whole-column reference is convenient when new records may be added below the existing data, because the formula already covers those rows. For a typical COUNTIF, it is a common and straightforward choice.
There are a few reasons to use a smaller range instead. A range such as B2:B5000 avoids including a heading, documents where the records are expected, and may be easier to audit in a workbook with many calculations. It also avoids counting an accidental matching value far below the visible data. Choose a limit that allows for realistic growth; a fixed range will not include new rows added beyond its endpoint.
If your records are in an Excel Table, a structured reference is another option. For a table named Table1 with a column named Status and the criterion in I2, use =COUNTIF(Table1[Status],I2). The formula counts the Status values and the table column expands as table rows are added. Replace Table1 and Status with the actual table and column names in your workbook.
Criteria, wildcards, and comparison operators
When I2 contains ordinary text, COUNTIF uses it as the criterion. You can also put a criterion directly in the formula, but a cell reference is easier to update and reuse. One important detail is that COUNTIF recognizes special characters in its criterion. An asterisk represents any sequence of characters, and a question mark represents one character. A tilde can be used to treat a wildcard character as a literal character.
For example, if B1:B6 contains city labels and you want to count entries containing the word East anywhere in the cell, the layout is city text in B1:B6. Use =COUNTIF(B:B,"*East*"). The asterisks allow text before or after East, so values such as Eastside and North East can match. This is a partial match, not an exact match. If the criterion should be treated as a literal asterisk or question mark, escape that character with a tilde in the criterion.
COUNTIF can also compare numbers using operators such as greater than, less than, or equal to. If C2:C20 contains numeric scores and the criterion is a score of at least 10, use =COUNTIF(C2:C20,">=10"). The quoted criterion combines the operator and the number. To make the threshold adjustable, put the number in I2; with scores in C2:C20 and the threshold in I2, use =COUNTIF(C2:C20,">="&I2). The ampersand joins the comparison text to the cell value.
Be aware that a value in I2 containing an asterisk, question mark, or comparison operator may be interpreted as a special criterion rather than as ordinary literal text. This is useful when intended, but can explain a surprising result if a search term includes those characters.
Common problems and how to diagnose them
- The answer is zero: Check that I2 contains the expected value and that the cells in B use the same spelling. Look for leading or trailing spaces, nonbreaking spaces copied from a web page, and numbers stored as text. A cell can look identical on screen while containing a different character or data type.
- The answer is larger than expected: Remember that a whole-column range includes the heading and every other row in B. Also check for repeated values in distant rows or for wildcard characters in I2 that make the criterion broader than an exact-looking search.
- The formula appears in the cell: The cell may be formatted as Text, or Excel may be showing formulas rather than results. Set the cell format to General, re-enter the formula, and check that Show Formulas is not enabled.
- The result does not update: Confirm that calculation is set to Automatic under Formulas > Calculation Options. If calculation is manual, Excel may not recalculate immediately after I2 or the data changes; press F9 to recalculate the workbook.
- A blank criterion produces an unexpected count: If I2 is empty, COUNTIF treats it as a blank criterion and can count blank cells in the range. A formula that returns an empty string may also be treated like a blank for this purpose. Make sure the criterion cell is populated if you do not intend to count blanks.
When a count seems wrong, inspect a few of the supposed matches directly and compare the underlying values, not only their displayed formatting. For example, a number formatted to show no decimal places can still have a fractional part, while a number stored as text may not behave like a numeric entry in every comparison. Cleaning inconsistent source data is often more reliable than trying to compensate for it with a more complicated counting formula.
COUNTIF compared with COUNTIFS and other options
COUNTIF handles one range and one criterion. If the task requires more than one condition, COUNTIFS is usually the natural extension. It counts rows only when all supplied criteria are met. For example, if B2:B20 contains owners, C2:C20 contains statuses, I2 contains an owner, and J2 contains a status, use =COUNTIFS(B2:B20,I2,C2:C20,J2) to count rows matching both the selected owner and selected status.
COUNTIFS is not simply a way to provide several alternative values for one column: its criteria are combined as conditions that must all match their corresponding ranges. For an either/or count, you may need to add separate COUNTIF results or design the criteria differently, taking care not to count the same row twice. If you only need one condition, COUNTIF is shorter and clearer.
For a count based on cell color, icon, or other formatting, COUNTIF is not the right tool because it evaluates values and criteria, not visual formatting. For a list of matching records rather than a numeric total, use a filtering feature or a dynamic-array function such as FILTER if your Excel version supports it. These are different tasks: COUNTIF answers “how many,” while filtering or a return-array formula answers “which ones.”
Frequently Asked Questions
Does COUNTIF(B:B,I2) count exact matches?
For ordinary text or numeric criteria, it counts cells matching the criterion; text matching is not case-sensitive. If I2 contains wildcard characters or a comparison expression, COUNTIF interprets those as criteria instructions, so the result may represent partial matches or a range of values rather than a literal exact match.
Does the formula include the heading in B1?
Yes. B:B includes B1 as well as all other rows in column B. The heading affects the result only if it matches the criterion. Use a range beginning below the heading, or a table column reference, when you want to count data records only.
Can I use a different search cell instead of I2?
Yes. Replace I2 with the cell that contains the criterion, or type a criterion directly into the formula. A cell reference is generally easier to maintain because changing the criterion does not require editing the formula.
Why does a blank I2 count empty cells?
An empty criterion is interpreted as a request to count blank cells in the selected range. Check I2 and the source column if the formula returns a count when you expected no result. You can also use a smaller, clearly defined data range so unrelated empty rows are not part of the count.
Can COUNTIF distinguish uppercase and lowercase?
No. COUNTIF’s text matching is case-insensitive, so capitalization alone does not make two entries different. If case-sensitive counting is essential, a different formula approach is needed; COUNTIF by itself cannot enforce that distinction.
Related
- Colour Cells in Excel: Fill, Conditional Rules, and More
- Combine Address, City, State, and ZIP in Excel
- Combine Excel Sheets Into One Sheet?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF in Excel: Practical Examples and FormulasIn Excel for Microsoft 365, SUMIF adds values that meet one condition. Its syntax is =SUMIF(range, criteria, [sum_range]);…
- How to Filter Multiple Values in Excel: A Practical GuideTo filter for several values in one Excel column, open its filter dropdown, clear (Select All), check each…
- OVER() in Snowflake: Window Function Syntax & ExamplesIn Snowflake, OVER() is the clause that turns a regular aggregate function (like SUM, COUNT, or AVG) into…
- How to Filter Negative Values in Excel: Filters & FormulasTo show only negative numbers in Excel, select your data range, choose Data > Filter, open the dropdown…
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Excel SUMIF Contains Specific Text: Formula & ExamplesTo sum values in Excel when a cell contains specific text, use SUMIF with wildcards: =SUMIF(range,"*text*",sum_range). For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.