With the text to search in A2:A8 and the target string Apple, use =COUNTIF(A2:A8,"Apple"); in the example list below, it returns 2. COUNTIF counts cells that match a text criterion, and you can adapt that criterion to find exact text, partial strings, or patterns with wildcards.
How COUNTIF interprets a string
COUNTIF uses the structure =COUNTIF(range,criteria). The range is the group of cells Excel checks, and the criteria tells Excel what to count. A string is text enclosed in straight double quotation marks when typed directly into a formula. The function returns a number: it counts qualifying cells, not the number of times a word appears within a cell.
For a consistent set of examples, imagine A2:A8 contains these seven entries: Apple, apple, Pineapple, Green apple, Apple pie, apples, and Pear. COUNTIF does not distinguish uppercase from lowercase, so Apple and apple both match the same text criterion. A cell containing Apple pie does not count as an exact match for Apple because the entire cell content is different.
Use the formula bar or enter the formula in an empty cell outside the range being counted. In Microsoft 365 for Windows, press Enter to calculate it. Excel for Mac also uses Enter in the usual case, though some keyboards label the key Return. Excel for the web uses the same formula syntax. Regional settings can affect whether formulas use commas or semicolons as argument separators; the formulas here use commas, as requested.
Count exact text or a partial string
A criterion with no wildcard characters looks for a whole-cell match. With the sample entries in A2:A8, the formula =COUNTIF(A2:A8,"Apple") returns 2: it counts Apple and apple, without regard to capitalization. It does not count Pineapple, Green apple, Apple pie, or apples. This is useful for category labels, names, and other values that should match the cell rather than merely occur inside it.
To count cells containing a string anywhere, add an asterisk before and after the search text. With the same entries in A2:A8, =COUNTIF(A2:A8,"*app*") returns 6. Every entry except Pear contains those letters in that order. The asterisk represents any sequence of characters, including no characters, so the pattern can match at the beginning, middle, or end of a cell.
Wildcard placement changes the result. With the same sample range, =COUNTIF(A2:A8,"app*") returns 4: Apple, apple, Apple pie, and apples begin with app. The formula =COUNTIF(A2:A8,"*apple") returns 4: Apple, apple, Pineapple, and Green apple end with apple. These patterns are handy when a list has extra words or prefixes and suffixes but shares a recognizable text fragment.
Use question marks and search for literal symbols
COUNTIF supports two wildcard characters. An asterisk (*) matches any number of characters. A question mark (?) matches exactly one character. For example, with product codes in B2:B20, =COUNTIF(B2:B20,"AB?7") counts codes with AB, one character of any kind, and then 7, such as ABX7. It will not match AB77 or ABXY7 because the pattern requires exactly one character between AB and 7.
Sometimes a cell contains an actual asterisk or question mark that you want to search for, rather than use as a wildcard. A tilde (~) escapes the next wildcard character. With text in A2:A20, =COUNTIF(A2:A20,"~*") counts cells whose entire content is a literal asterisk. To find an asterisk anywhere in a cell, use a wildcard on either side of the escaped character: =COUNTIF(A2:A20,"*~**"). The tilde makes the middle asterisk literal; the first and last asterisks remain wildcards.
Build the criterion from a cell
You do not have to type the search text into every formula. Put a search term in a separate cell and join it to wildcard characters with the ampersand operator. Suppose A2:A8 contains the sample fruit entries and D2 contains app. The formula =COUNTIF(A2:A8,"*"&D2&"*") returns 6, because it counts the entries containing app. This setup makes it easy to change the search term without editing the formula.
The ampersand joins text pieces together. In that example, Excel combines a leading asterisk, the value in D2, and a trailing asterisk to create the criterion *app*. If D2 is empty, the resulting criterion is two wildcards; because an asterisk can match zero or more characters, that pattern can match every text entry. Check that the search cell is populated when an empty search would produce a misleading count.
Criteria can also include comparison operators such as greater than or not equal to. For a text search that combines a fixed prefix with a value in another cell, use wildcards and concatenation as above. For numeric comparisons, the criterion is typically built by joining the operator to a cell reference; keep in mind that numbers stored as text may behave differently from actual numeric values. COUNTIF is designed for one range and one criterion, not for comparing two separate columns row by row.
Case sensitivity and text matching limits
COUNTIF is not case-sensitive. It treats Apple, APPLE, and apple as matches for the same exact-text criterion. This is usually helpful when names or categories have inconsistent capitalization, but it is a limitation if capitalization carries meaning in the data.
For a case-sensitive count, use EXACT inside SUMPRODUCT instead. With the seven example entries in A2:A8, =SUMPRODUCT(--EXACT(A2:A8,"Apple")) returns 1, because only the entry Apple has exactly that capitalization and content. EXACT compares text case-sensitively, and the double unary operator turns its TRUE/FALSE results into 1s and 0s for SUMPRODUCT to add. For a larger dataset, replace A2:A8 with the relevant range.
COUNTIF also does not provide a way to count only part of a word at a particular position unless the wildcard pattern expresses that position. A pattern such as *app* finds the letters anywhere, including inside Pineapple. If you need to distinguish a standalone word from letters embedded in a longer word, COUNTIF alone may not be enough; the data may need cleaning or a more tailored formula.
COUNTIF, COUNTIFS, and other approaches
Choose COUNTIF when one condition is sufficient, such as counting entries that contain a certain string. Use COUNTIFS when a count must satisfy multiple conditions, for example a text category and a date range at the same time. COUNTIFS applies all its criteria together, and its criteria ranges need to correspond in size and shape. It does not make a single text criterion case-sensitive.
For a quick visual review, Excel’s Find feature can locate text, but it is not a live count formula. Filtering a table can also show matching rows, which is useful for inspection, while COUNTIF is better when a result should update in a worksheet cell as the source data changes. For complex text rules, helper columns or formulas using functions such as SEARCH may be clearer than an increasingly complicated COUNTIF criterion.
COUNTIF is available in Microsoft 365 on Windows, Mac, and the web, with the same basic syntax. The exact ribbon locations for filtering and finding can vary by platform, but no special add-in is needed to use the function. If a workbook is shared with people using older Excel versions, COUNTIF is a broadly compatible choice compared with newer functions that may not be available in every release.
Common COUNTIF string problems
If the result is zero when a match seems obvious, check the criterion and the source cells before changing the formula. A few characters that look identical can differ, and extra spaces or a wildcard in the wrong position can alter what COUNTIF considers a match.
- Unexpected spaces: A trailing space in a cell can prevent an exact match. TRIM can remove ordinary leading, trailing, and repeated spaces in a helper column, but it does not remove every kind of nonbreaking space.
- Wildcards used unintentionally: A question mark or asterisk in a criterion is a wildcard unless escaped with a tilde. This can make the count larger than expected.
- Wrong range: Confirm that the range includes the data rows and does not accidentally include a heading or omit the latest entries.
- Number versus text: A value that looks numeric may be stored as text. Decide whether the task is to match text or count numeric values, and make the source data consistent if needed.
- Formula-generated blanks: A cell containing a formula that returns an empty string is not the same thing as a truly empty cell in every counting situation. Test the workbook’s actual data if blank counts matter.
- Locale separators: If Excel rejects a formula with commas, the computer’s regional settings may require semicolons between arguments. Use the separator Excel expects on that installation.
When debugging, temporarily test a simple exact criterion on a small range, then add wildcards or cell references one part at a time. Also check the formula bar for the actual cell contents; what appears on screen may be affected by formatting and can hide spaces or differences in stored values.
Frequently Asked Questions
Does COUNTIF match text exactly? Without wildcard characters, COUNTIF matches the whole cell value, but it ignores capitalization. A criterion of Apple matches Apple and apple, not Pineapple or Apple pie.
How do I count cells that contain a word? Put an asterisk on both sides of the search text in the criterion, such as =COUNTIF(A2:A20,"*word*"). This counts cells with the string anywhere in their contents. Replace the range and word with the ones in your worksheet.
Can COUNTIF count text that starts or ends with certain letters? Yes. Put the asterisk after the prefix to match text that starts with it, or before a suffix to match text that ends with it. For example, app* matches strings beginning with app, while *apple matches strings ending with apple.
Is COUNTIF case-sensitive? No. COUNTIF treats uppercase and lowercase letters as equivalent. For a case-sensitive comparison, EXACT can be combined with SUMPRODUCT, as shown above.
Why does COUNTIF return zero for a word I can see? The cell may contain extra spaces, a different character, or text outside the selected range. The criterion may also be using an unintended wildcard. Inspect the underlying value and test the formula on a small range to identify the mismatch.
Related
- Excel Solver for Data Analysis: Setup, Models, and Limits
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
- Cash Flow Format in Excel: Build a Simple Statement
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Time Duration: Calculate Hours EasilyIf the start time is in A2 and the end time is in B2, subtract the start from…
- Filter Excel Table by Text: Steps, Options, and FixesTo filter an Excel table by text in Microsoft 365 for Windows, select the drop-down arrow in the…
- Excel Formula for Number of Days Between Two DatesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel Merge Data From 2 Cells: Formulas and MethodsTo merge the contents of two cells in Excel, combine them with a formula such as =A2&" "&B2.…
- XLOOKUP Advanced Examples for Real-World Excel TasksXLOOKUP advanced examples include returning several columns at once, matching on multiple conditions, finding the last occurrence, and…
- Excel INDEX and AVERAGE: How to Combine ThemExcel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.