Yes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the text to search for in A2:A10, this formula counts cells containing “east” anywhere in the cell: =COUNTIF(A2:A10,"*east*"). The asterisks allow any characters before and after the text.
How COUNTIF matches partial text
COUNTIF takes a range and one condition, also called a criterion. For a partial-text match, the criterion combines the text with wildcard characters. The asterisk (*) stands for any number of characters, including no characters, while the question mark (?) stands for exactly one character.
For example, *east* matches “East,” “Northeast,” and “east coast.” Excel’s COUNTIF matching is not case-sensitive, so capitalization does not affect those results. The formula counts cells in the range that meet the criterion; it does not count how many times the text occurs inside each cell. If one cell says “east, east,” that cell contributes one to the count.
The wildcard is part of the criterion, not a separate function or setting. You can put the criterion directly in the formula as text, or assemble it from a cell reference when the search term needs to change.
Count text anywhere, at the beginning, or at the end
Where you place the asterisk determines what kind of partial match Excel looks for. Assume A2:A8 contains “Northeast,” “East,” “east coast,” “Southeast,” “West,” a blank cell, and “Eastern.” The following formulas show common patterns:
- With those values in A2:A8,
=COUNTIF(A2:A8,"*east*")counts text containing “east” anywhere. It returns 5: Northeast, East, east coast, Southeast, and Eastern. - With the same values in A2:A8,
=COUNTIF(A2:A8,"east*")counts text beginning with “east.” It returns 3: East, east coast, and Eastern. - With the same values in A2:A8,
=COUNTIF(A2:A8,"*east")counts text ending with “east.” It returns 3: Northeast, East, and Southeast.
The empty cell does not match any of these criteria. Notice that “Eastern” matches the begins-with pattern because its first four letters are east, even though the whole word is not “East.” Similarly, the contains pattern can find the letters within a larger word. If you need whole-word matching rather than a substring, a basic wildcard criterion may be too broad; see the alternatives below.
Use a cell reference for a changeable search term
A formula with a typed-in word is easy to read, but changing the word means editing the formula. Put the term in a separate cell instead—for example, D2—and join it to the wildcard symbols with ampersands. This is useful for a small report where someone will enter different search terms.
With the text to search in D2 and the list in A2:A10, use =COUNTIF(A2:A10,"*"&D2&"*"). If D2 contains “east,” the formula counts cells containing that text anywhere. The ampersands join the asterisk, the contents of D2, and the final asterisk into one criterion. Leave the wildcard symbols inside quotation marks; the cell reference itself should not be in quotation marks.
If D2 is empty, the joined criterion becomes two asterisks, which can match any text value. That may produce a count you do not intend. To show no result until a term is entered, with data in A2:A10 and the search term in D2, use =IF(D2="","",COUNTIF(A2:A10,"*"&D2&"*")). This returns a blank-looking result while D2 is empty, and runs the count when a term is present.
Use the question mark and escape literal wildcards
The question mark is useful when a character can vary but the number of characters is known. For example, a criterion such as Sam? matches five-character text values that begin with “Sam,” such as “Sami” or “Sam1”; it does not match “Sam” because that has no character after the m. Add an asterisk if additional characters may follow.
Sometimes the cell contains a real asterisk or question mark that you want to find as a character, not use as a wildcard. In Excel criteria, a tilde (~) escapes the next wildcard character. With the values in A2:A10, this formula counts cells containing a literal asterisk: =COUNTIF(A2:A10,"*~**"). Read the criterion as: any characters, a literal asterisk, then any characters. To search for a literal question mark, use ~? in the criterion.
A literal tilde also needs escaping. Use ~~ in the criterion when you want to match a tilde character. These escape rules matter when importing product codes, filenames, or notes that may include punctuation; otherwise a wildcard can match more than the literal symbol you meant to locate.
Count partial text with more than one condition
COUNTIF accepts one condition. If a row must meet multiple conditions, use COUNTIFS, which applies criteria to corresponding ranges. For example, suppose department text is in A2:A20 and a status is in B2:B20. To count rows where the department contains “north” and the status is exactly “Open,” use =COUNTIFS(A2:A20,"*north*",B2:B20,"Open"). Each range should cover the same rows so the conditions are checked row by row.
You can combine partial-text and exact criteria, or use partial text in more than one range. For instance, a report could count rows where a product description contains a search term and the adjacent region contains another. Each criterion is paired with its own range in COUNTIFS. This is different from asking whether either of two words appears: multiple criteria in COUNTIFS are combined as AND conditions, so every condition must be met.
For an OR condition—such as counting cells that contain either “east” or “west”—one simple approach is to add two counts. With the list in A2:A10, use =COUNTIF(A2:A10,"*east*")+COUNTIF(A2:A10,"*west*"). This works when a cell cannot match both terms, or when counting a cell twice if it contains both is acceptable. If one cell can contain both and must count only once, adding the results will double-count that cell; a different formula or a helper column is needed.
Case sensitivity and whole-word matching
Excel’s COUNTIF does not distinguish uppercase from lowercase. A criterion for “east” also matches “EAST” and “East.” This is often helpful for ordinary lists, but it is not suitable when capitalization is meaningful, such as distinguishing case-sensitive codes.
For a case-sensitive substring count, FIND can search each cell and SUMPRODUCT can total the successful matches. With values in A2:A10 and the case-sensitive term “East,” use =SUMPRODUCT(--ISNUMBER(FIND("East",A2:A10))). It counts cells where FIND locates that exact capitalization, regardless of where the text occurs. FIND returns an error for cells without the term; ISNUMBER turns successful positions into TRUE values, and the double unary converts them to numbers for the sum. Unlike COUNTIF, this method is case-sensitive.
Partial-text matching can also find a sequence inside a longer word. If you need a whole word, define what counts as a word boundary in your data: spaces, commas, hyphens, or other punctuation may separate terms. A simple criterion with asterisks does not understand linguistic word boundaries. For consistently formatted data, a helper column that normalizes or separates words can make the rule clearer and easier to audit.
Common problems and checks
If a count is unexpectedly zero or too large, check the range and the criterion before changing the formula. A few small data issues account for many confusing results:
- Missing wildcards:
COUNTIF(A2:A10,"east")seeks an exact text match. Use*east*when the text can appear within a longer cell value. - Wrong wildcard placement:
east*means begins with;*eastmeans ends with;*east*means contains. - Extra spaces: A leading or trailing space in a cell or in the search-term cell can affect the match. Inspect the source values, and consider cleaning imported data when appropriate.
- Text stored differently: Imported content may contain nonprinting characters or unusual spaces. A cell can look like it contains the expected word but still fail to match it exactly as typed.
- Wrong range: Confirm that the formula includes all intended rows and excludes headings. When using
COUNTIFS, make sure the criteria ranges have matching dimensions. - Numbers and text: Wildcards are designed for text matching. If the goal is to test a numeric condition, use a numeric criterion such as greater than a value rather than trying to search a number with text wildcards.
To diagnose a formula, first test it against a short range whose contents you can inspect. Then verify the exact characters in the search term, including spaces and punctuation. If the source is messy, clean a copy or use a helper column rather than silently changing the original data.
COUNTIF versus Find and Filter
Use COUNTIF when the goal is a count that updates as the data changes. It leaves the source list in place and returns a number in the formula cell. A filter is more useful when you want to inspect the matching rows or work with them individually. In Excel for Microsoft 365 on Windows, select the data and use the filter controls on the Data tab, then filter the relevant column by a text condition such as “contains.” The filter shows records; it does not replace a reusable count formula.
Find is a quick way to locate text manually, but it is not a calculated summary. A helper column can be better than a complicated formula when people need to see why each row qualifies. For example, a helper column can label descriptions that contain a search term, and a separate count can summarize those labels. Choose based on whether you need a live total, visible matching records, or a row-by-row explanation.
The wildcard formulas described here work in Excel for Microsoft 365 on Windows and are also available in current Excel for Mac and Excel for the web. The interface for filtering or editing cells can differ across platforms, but the basic COUNTIF syntax is the same. If a workbook will be shared, avoid relying on newer functions for this simple task; COUNTIF has long-standing Excel support.
Frequently Asked Questions
How do I count cells that contain a word in Excel?
With the values in A2:A10, use =COUNTIF(A2:A10,"*word*"), replacing “word” with the text you want to find. The asterisks allow other characters before and after it.
Does COUNTIF partial text match ignore capitalization?
Yes. COUNTIF is not case-sensitive, so a criterion for “cat” also matches “Cat” and “CAT.” Use a case-sensitive approach such as FIND with SUMPRODUCT when letter case must matter.
How do I count cells that start with certain letters?
Put the text first and an asterisk after it. With data in A2:A10, =COUNTIF(A2:A10,"pre*") counts cells beginning with “pre,” including longer words such as “prefix.”
Can COUNTIF search for an asterisk or question mark literally?
Yes. Put a tilde before the wildcard character in the criterion: ~* for a literal asterisk or ~? for a literal question mark. Use asterisks around the escaped character too if it may appear anywhere in the cell.
Can COUNTIF count two partial-text terms at once?
For an either/or count, add two COUNTIF results. Check whether a cell could contain both terms, because addition counts that cell twice. For conditions that must both be true across separate columns, use COUNTIFS.
Related
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
- Data Cleaning Tools in Excel: What to Use and When
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query Date Functions: A Practical GuidePower Query date functions are M-language functions for converting, extracting, comparing, and adjusting dates and times as data…
- Excel Insert a New Row and Keep FormulasTo insert a row in Excel and keep formulas, select the row number where the new row should…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
- Why Is HLOOKUP Used in Excel? Uses and Examples ExplainedHLOOKUP is used in Excel to find a value in the top row of a table and return…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.