Yes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple") for a normal exact text match, or =COUNTIFS(A2:A100,"*Apple*") to find Apple anywhere in a cell. Text matching with COUNTIFS is not case-sensitive, and asterisks and question marks act as wildcards.
What COUNTIFS does with text
COUNTIFS counts rows where every supplied condition is true. Each condition consists of a range to check and a criterion describing what qualifies. Although its name suggests it is mainly for numbers, its criteria can be text, comparison operators, wildcard patterns, or values stored in other cells.
For one text condition, provide one range and one criterion. With text in A2:A100, the formula =COUNTIFS(A2:A100,"Apple") counts cells whose text matches Apple. The comparison ignores letter case, so Apple, APPLE, and apple qualify. It does not count Apple Pie or Pineapple as exact matches, unless the criterion includes wildcards.
COUNTIFS can also test several conditions at once. It evaluates corresponding positions in the ranges: the first cell in each range belongs to one record, the second cells to the next record, and so on. A row is counted only when all its criteria are satisfied. This makes the function useful for questions such as “How many orders have the product Apple and the status Shipped?”
Count an exact text match
For a straightforward exact match, put the text criterion in straight double quotation marks. Assume product names are in A2:A8 and the values are Apple, Apple Pie, apple, Pineapple, Pear, Apple, and a blank cell. With that layout, use =COUNTIFS(A2:A8,"Apple"). The result is 2: the two cells containing Apple or apple. COUNTIFS does not distinguish their capitalization.
“Exact” here means the cell’s text matches the criterion rather than merely containing it. It does not mean that COUNTIFS is case-sensitive, nor does it prevent wildcard characters in the criterion from being interpreted as wildcards. If a cell appears to match but is not counted, inspect the underlying content. Leading or trailing spaces, nonbreaking spaces copied from a webpage, and other invisible characters can make text look the same while differing from the criterion.
A practical way to avoid retyping a criterion is to put the desired text in a cell. Suppose A2:A100 contains product names and C2 contains Apple. With that layout, use =COUNTIFS(A2:A100,C2). This makes the result change when C2 changes and is convenient for a small report with a user-selected product. If C2 is blank, the criterion is blank, so the formula will count blank-looking entries rather than return a count of all products.
Count text that contains a word or fragment
Use an asterisk (*) to represent any number of characters, including no characters. Placing one before and after a word finds that text anywhere in the cell. With product names in A2:A100, =COUNTIFS(A2:A100,"*Apple*") counts names such as Apple, Apple Pie, and Pineapple. It also counts apple because the comparison is case-insensitive.
Wildcard placement changes what qualifies:
"Apple*"matches text that starts with Apple, such as Apple Pie."*Apple"matches text that ends with Apple, such as Green Apple."*Apple*"matches text with Apple anywhere, including Pineapple."Ap?le"uses ? for exactly one character, so it can match Apple or ample.
When the search term is stored in a cell, join it to wildcard characters with ampersands. Suppose A2:A100 contains product names and C2 contains Apple. With that layout, use =COUNTIFS(A2:A100,"*"&C2&"*"). The result counts cells containing the text in C2. This is helpful for a search box or a summary sheet where the term changes frequently. If C2 itself contains * or ?, those characters are still treated as wildcards; see the escaping section below if they should be literal.
Combine text matching with other conditions
Every additional COUNTIFS condition narrows the count: a row must meet all criteria. The ranges must correspond to the same records and have compatible dimensions. Avoid pairing a full-column range with a short range, because the function needs aligned ranges for its conditions.
For example, suppose product names are in A2:A100, regions in B2:B100, and order statuses in C2:C100. To count shipped Apple orders from the West region, use =COUNTIFS(A2:A100,"Apple",B2:B100,"West",C2:C100,"Shipped"). This counts rows where the product, region, and status all meet their criteria; a row with Apple in the East does not qualify.
You can mix exact text criteria with partial text criteria. With descriptions in A2:A100 and statuses in B2:B100, use =COUNTIFS(A2:A100,"*repair*",B2:B100,"Open") to count open records whose descriptions contain repair. Comparison operators can also be joined to cell values. With dates in B2:B100 and a cutoff date in D2, =COUNTIFS(B2:B100,">="&D2) counts dates on or after the date in D2. The operator is text in the criterion, so the ampersand joins it to the cell value.
Make wildcard characters literal
The wildcard rules are useful for searching, but they matter when the text itself contains an asterisk or question mark. In COUNTIFS criteria, a tilde (~) escapes a wildcard. Use ~* to look for a literal asterisk, ~? for a literal question mark, and ~~ for a literal tilde. For example, if codes are in A2:A100, =COUNTIFS(A2:A100,"Plan~*") counts cells whose text is Plan* rather than treating the asterisk as “any following characters.”
For a literal asterisk anywhere in a cell, surround the escaped character with wildcards. With descriptions in A2:A100, =COUNTIFS(A2:A100,"*~**") searches for any text, then a literal asterisk, then any following text. This distinction is particularly useful with product codes, file names, and notes that use symbols as ordinary characters.
Case sensitivity and text cleanup
COUNTIFS is not case-sensitive. There is no case-sensitive switch to add to its criteria, so a criterion of Apple treats APPLE and apple as matches too. For most category, status, and name counts, this is desirable: capitalization differences do not split what is logically the same label.
If capitalization is meaningful, use a case-sensitive alternative rather than relying on COUNTIFS. Suppose A2:A100 contains the text to check. With that layout, =SUMPRODUCT(--EXACT(A2:A100,"Apple")) counts cells that match Apple with the same capitalization. EXACT distinguishes upper- and lowercase letters, and SUMPRODUCT totals its TRUE/FALSE results. This checks exact text only; it is not a direct case-sensitive version of COUNTIFS with multiple criteria.
When apparent matches are missed, first check for extra spaces and inconsistent imported data. If the source values are in A2:A100, you can clean a value in a helper column by entering =TRIM(A2) in B2 and filling it down. TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces to one. It does not remove every unusual whitespace character, such as some nonbreaking spaces, so data copied from the web may need additional cleanup. Keep the original column until you have checked the cleaned results.
Common COUNTIFS text-matching problems
- The count is too high: A criterion such as
"*cat*"also matches words such as category and concatenation. Use an exact criterion or a more specific pattern if you need to narrow the results. - The count is zero: Check the spelling, spaces, selected range, and whether the criterion cell contains the value you expect. Also verify that the range covers the data rows rather than a heading or a different column.
- A wildcard in the data behaves unexpectedly: Decide whether * or ? is intended as a search operator or as a literal character, then escape literal characters with ~.
- Multiple criteria produce a smaller result than expected: COUNTIFS applies AND logic. A row must meet every condition. If you want either one condition or another, use separate counts or another approach rather than expecting COUNTIFS to apply OR logic automatically.
- The formula shows an error: Check that each criteria range has the same number of rows and that quotation marks and parentheses are balanced. Excel formulas commonly use commas as separators, but some regional settings use semicolons instead.
Another frequent source of confusion is a criterion written with an operator. For example, a criterion of "<>Apple" means “not equal to Apple,” not “contains something other than Apple.” COUNTIFS text comparisons are criteria-based; use wildcards when you mean contains, starts with, or ends with.
COUNTIFS versus COUNTIF and other options
COUNTIF is the simpler choice when you need one condition; COUNTIFS is designed for multiple conditions. Both support text criteria and wildcards, and both are case-insensitive. Using COUNTIFS for one condition is still valid, and it can make a formula easier to expand later when you add a region, date, or status requirement.
For case-sensitive exact text, EXACT combined with SUMPRODUCT is a suitable alternative, as shown above. For messy source data, cleaning the text in a helper column may be clearer than building increasingly complicated criteria. The best choice depends on the question: COUNTIFS is direct for ordinary exact or wildcard text counting, while a helper column or case-sensitive test addresses a different requirement.
In Microsoft 365 for Windows, enter the formula in a worksheet cell and press Enter; Excel calculates the result. The same COUNTIFS function is available in Excel for Mac and Excel for the web, though interface details can vary. If a formula copied from a webpage uses smart quotation marks, replace them with straight double quotes. Curly quotes are not valid formula delimiters.
Frequently Asked Questions
Does COUNTIFS match text exactly?
Yes, a plain text criterion such as "Apple" matches Apple rather than Apple Pie or Pineapple. The comparison is case-insensitive, and wildcard characters in a criterion retain their wildcard meaning unless escaped.
How do I count cells containing a word in Excel?
Use an asterisk on both sides of the word, such as "*repair*", as the COUNTIFS criterion. This finds the word or fragment anywhere in a cell, regardless of capitalization.
Can COUNTIFS distinguish uppercase and lowercase text?
No. COUNTIFS treats Apple, APPLE, and apple as equivalent text matches. For case-sensitive exact matching, use EXACT with SUMPRODUCT or another case-sensitive method.
How do I use a cell reference as the text criterion?
Use the criterion cell directly, such as =COUNTIFS(A2:A100,C2). For a contains search, combine wildcards with the cell reference, as in =COUNTIFS(A2:A100,"*"&C2&"*").
Why does COUNTIFS return zero when the text looks identical?
Possible causes include extra spaces, hidden characters, a different range, or a misspelled criterion. Check the underlying values and clean imported text where needed; also remember that COUNTIFS ignores case but not every difference in the characters stored in a cell.
Related
- Excel Timesheet Format: Build a Reliable Weekly Sheet
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Install XLOOKUP in Excel and Check CompatibilityYou do not install XLOOKUP as a separate Excel feature or add-in: it is already built into supported…
- Excel COUNTIF Range Examples: Text, Numbers, and DatesUse Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts…
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
- Excel Filter by Cell Colour: Steps, Limits and FixesTo filter by cell colour in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Concat Excel Date Format: Keep Dates ReadableTo concatenate a date in Excel and keep it readable, convert the date to formatted text with TEXT…
- Calculate Age in Excel With a Birthday Date FormulaTo calculate someone’s age in completed years in Excel, enter their birthday as a real date in A2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.