Excel has no SUMIF function that adds text strings, but in Microsoft 365 you can use TEXTJOIN with FILTER to return the text values whose rows meet a condition. With categories in A2:A10, text to return in B2:B10, and the category to find in E2, use this formula:
With categories in A2:A10, the text entries to return in B2:B10, and the criterion in E2, enter:
=TEXTJOIN(", ",TRUE,FILTER(B2:B10,A2:A10=E2,"No matches"))
If E2 contains North, and the matching entries in column B are Blue, Amber, and Green, the result is Blue, Amber, Green in one cell. This is the closest common text equivalent to SUMIF: FILTER selects matching rows, and TEXTJOIN combines their text.
What SUMIF Does—and What It Cannot Do
SUMIF tests a range against a criterion and adds values from a corresponding sum range. For example, it can add all sales associated with the category North. Its result is a number, so it is not designed to combine words or labels. If you give SUMIF text values to add, it does not concatenate them into a sentence or list.
The right replacement depends on what you mean by “for text.” You might want to collect the text from matching rows, count how many matches exist, or check whether any text meets a condition. These are different tasks and call for different formulas. In particular, COUNTIF can count cells that meet a criterion, but it does not return the matching text itself.
For a text list, the usual pairing is FILTER and TEXTJOIN. FILTER returns the matching values as an array; TEXTJOIN puts those values into one cell with a separator such as a comma and space. Both are available in Microsoft 365 and Excel 2021 or later. This guidance focuses on Microsoft 365 for Windows; the formulas also work in current Excel for Mac and Excel for the web, though ribbon details and some interface labels can differ.
How the TEXTJOIN and FILTER Formula Works
In the example, A2:A10 contains categories, B2:B10 contains the text to collect, and E2 contains the category being searched for. The condition A2:A10=E2 checks each category cell against the selected value and produces a set of TRUE or FALSE results. FILTER uses those results to keep the corresponding cells from B2:B10.
TEXTJOIN then combines the filtered values. Its first argument, “, “, is the delimiter placed between entries. TRUE tells it to ignore empty cells, which is useful if some matching rows have no text in column B. The final argument in FILTER, No matches, is the value to return when nothing meets the condition. Without that optional fallback, FILTER returns an error when it finds no rows.
The ranges used for the condition and the returned values need to line up: A2:A10 and B2:B10 each contain nine rows. If one range starts or ends on a different row, Excel may return an error or associate the wrong values. When copying the formula, consider whether to lock the data ranges with dollar signs if you plan to fill it across or down.
Collecting Text with Different Matching Rules
The basic formula uses an exact, case-insensitive comparison. North matches North, but not Northeastern. Excel’s ordinary equality comparison does not distinguish uppercase from lowercase, so North and NORTH count as a match. If exact matching is what you want, the basic version is usually the clearest choice.
To match a category when it contains a phrase, rather than equals it, use SEARCH to look for the criterion inside each category. With categories in A2:A10, text entries in B2:B10, and the phrase to find in E2, use:
=TEXTJOIN(", ",TRUE,FILTER(B2:B10,ISNUMBER(SEARCH(E2,A2:A10)),"No matches"))
SEARCH returns a position when it finds the phrase and an error when it does not. ISNUMBER converts those results to TRUE or FALSE values that FILTER can use. SEARCH is not case-sensitive. For instance, if E2 contains north, this can match both North Region and NORTH Zone. If you need case-sensitive searching, Excel’s FIND function is case-sensitive, though you would need to adapt the condition and handle its errors appropriately.
You can also apply more than one condition. For example, a condition can require both a category match and a status match. With categories in A2:A10, statuses in C2:C10, text entries in B2:B10, and the chosen category and status in E2 and F2, respectively, use:
=TEXTJOIN(", ",TRUE,FILTER(B2:B10,(A2:A10=E2)*(C2:C10=F2),"No matches"))
Multiplying the two TRUE/FALSE tests acts like AND: a row is kept only when both tests are TRUE. For OR logic—where either condition can qualify a row—add the tests instead. Make sure the ranges in each test cover the same rows as the return range.
When You Need a Count, a Unique List, or a Check
If the goal is to count records matching a category, COUNTIF is the direct option. With categories in A2:A10 and the criterion in E2, enter:
=COUNTIF(A2:A10,E2)
If E2 is North and North appears in four cells in A2:A10, the formula returns 4. This counts matching category cells; it does not count only nonblank text in column B. To count rows that match the category and have a nonblank entry in B, use COUNTIFS. With the same category and return ranges, enter:
=COUNTIFS(A2:A10,E2,B2:B10,"<>")
The criterion <> means not equal to blank in this context. COUNTIFS counts rows meeting both conditions; it does not combine the text values.
If you want each matching text value only once, wrap FILTER in UNIQUE. With categories in A2:A10, text in B2:B10, and the criterion in E2, use:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B10,A2:A10=E2,"")))
UNIQUE removes repeated entries from the filtered result. This can help when a label appears on multiple matching rows but you want a compact list of labels. If the order also matters, SORT can be added around UNIQUE, provided you are using Microsoft 365 or Excel 2021 or later. Remember that removing duplicates changes the meaning: it is appropriate for a distinct list, not for a record-by-record report.
Choosing the Right Formula for the Job
Use the output you need to guide the choice. TEXTJOIN with FILTER is suitable when you want several matching text entries displayed together. COUNTIF or COUNTIFS is suitable when you need a numeric count. A simple IF or a lookup may be better if you need one answer, such as the first matching description, rather than a list of every match.
- Return all matching text: use FILTER and, if you want one cell, TEXTJOIN.
- Count matching rows: use COUNTIF for one condition or COUNTIFS for multiple conditions.
- Return distinct matching text: use UNIQUE with FILTER.
- Sum numbers for matching text criteria: use SUMIF or SUMIFS, with a numeric sum range.
- Find whether a match exists: use COUNTIF and test whether its result is greater than zero.
A common misconception is that a formula can both add numbers and concatenate text in the same SUMIF operation. These are separate aggregations. If you need a total and a list for the same category, make two formulas: one SUMIF or SUMIFS formula for the numeric total and one TEXTJOIN/FILTER formula for the text list. Keeping the results separate also makes the worksheet easier to audit.
Common Problems and Practical Fixes
If the formula says No matches unexpectedly, check that the criterion is really identical to the category value. Extra spaces, inconsistent spelling, or a number stored as text can prevent an exact match. TRIM can remove ordinary leading and trailing spaces from data, but it does not automatically repair every kind of imported whitespace. It is often better to clean the source column once than to make every reporting formula more complicated.
If FILTER returns an error for a missing match, make sure its third argument is present and quoted as text. In the opening formula, No matches is a deliberate fallback; it is not an Excel error message. You can replace it with an empty string if a blank result is preferable. That can make a report look cleaner, but it also makes “no results” less obvious to someone reading the sheet.
If the formula returns the right items in an unexpected order, remember that FILTER keeps the source order; it does not sort the results. Sort the source data or add SORT when an alphabetical or numeric order is required. If results repeat, decide whether repetitions represent separate records. Use UNIQUE only when duplicates should genuinely be removed.
TEXTJOIN places the combined output in a single cell, and Excel cells have a maximum length of 32,767 characters. A very large number of matches can exceed that limit and produce an error. For long reports, it is usually more useful to let FILTER spill the matching rows into neighboring cells instead of joining every value into one long string. Ensure the cells where the results need to spill are empty; otherwise Excel can report a spill error.
Finally, formula separators depend on regional settings. The examples use commas, as required for the standard English-language syntax shown here. Some Excel installations use semicolons between arguments because of locale settings. If Excel rejects a correctly copied formula, check the list separator configured for your system rather than changing the function names.
Frequently Asked Questions
Is there a SUMIF function that concatenates text?
No. SUMIF adds numbers; it does not join text. For matching text values, use FILTER to select the relevant cells and TEXTJOIN to combine them into one cell. If you only need the number of matches, use COUNTIF instead.
Can I use wildcards with a text SUMIF-style formula?
For counting or summing, COUNTIF, COUNTIFS, SUMIF, and SUMIFS support criteria such as an asterisk for multiple characters and a question mark for one character. In a FILTER condition like A2:A10=E2, those wildcard characters are treated as ordinary characters, not as wildcard instructions. For partial text matching with FILTER, SEARCH is a practical option.
How do I return matching text in separate cells instead of one long cell?
Use FILTER by itself rather than wrapping it in TEXTJOIN. With the same aligned category and text ranges and criterion, FILTER returns the matching values as a spill range below or beside the formula. Leave the destination cells clear so the results have room to appear.
Does the formula distinguish uppercase and lowercase?
No. The equality test used in the basic formula is not case-sensitive, and SEARCH is also not case-sensitive. For a case-sensitive comparison, use EXACT for direct comparisons or FIND for text searching, and build the resulting TRUE/FALSE condition into FILTER.
Why does my formula show #NAME? or fail to calculate?
Check that the Excel version supports the functions and that their names and argument separators match your installation. FILTER is available in Microsoft 365 and Excel 2021 or later. Also confirm that each function name is spelled correctly and that your regional settings do not require semicolons instead of commas.
Related
- Excel Dependent Drop-Down List: 3 Levels
- Excel Dependent Drop-Down Lists with Dynamic Ranges
- Excel Dependent Drop-Down Lists in Tables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- Excel Table Filter Out Duplicates: Keep One Copy SafelyYes—but Excel’s regular table filter does not have a built-in “show only unique rows” switch. To keep one…
- Excel Filter for Duplicate Values: Find and Review ThemTo filter duplicate values in Excel for Microsoft 365 on Windows, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill…
- Excel CLEAN Function: Remove Nonprinting CharactersTo remove nonprinting characters from text in Excel, enter =CLEAN(A2) in a helper cell when the source text…
- Excel Formula or Operator? Understand the DifferenceIn Excel, a formula is an expression that calculates a result, while an operator is a symbol that…
- Concatenate 2 Dates in Excel: Formulas and FormattingTo concatenate two dates in Excel, convert each date to readable text with TEXT, then join the results…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.