Excel SUMIF: Sum Columns When a Cell Contains Text

To add values in one column only when the corresponding cell in another column is exactly x, use Excel’s SUMIF function. For example, if labels are in A2:A10 and amounts are in B2:B10, enter =SUMIF(A2:A10,"x",B2:B10); Excel totals the amounts on rows where column A contains x.

How SUMIF matches text and adds values

SUMIF checks a range against one condition, then adds values from a corresponding range wherever that condition is met. Its arguments are the range to check, the condition, and—when the values to add are elsewhere—the sum range. The first and third arguments can be the same range if you want to add cells that themselves meet the condition.

For a common worksheet, each row represents one record: a product, expense, task, or sale. One column holds the text label and another holds the number. The formula compares the label in each row with x, then includes that row’s number in the total if it matches. It does not add the text cells themselves.

With categories in A2:A10 and amounts in B2:B10, =SUMIF(A2:A10,"x",B2:B10) is the basic pattern. The text criterion is enclosed in straight double quotation marks. If rows 2, 5, and 8 have x in column A and the corresponding B values are 12, 7, and 20, the result is 39.

Build the formula from your worksheet layout

Identify the condition column and the values column before writing the formula. In the example below, column A contains a label and column B contains the amount. Keep the two ranges aligned so that the label and amount on each row belong to the same record.

Range: the cells Excel should inspect for the text, such as A2:A10.
Criteria: the value to match, such as the text “x” or a cell reference containing x.
Sum range: the numbers to add on matching rows, such as B2:B10.

With labels in A2:A10, amounts in B2:B10, and the criterion entered in D1, use =SUMIF(A2:A10,D1,B2:B10). This is useful when you want to change the target text without editing the formula. If D1 changes from x to y, the same formula calculates the total for y.

If the sum range is omitted, Excel adds the cells in the criteria range that meet the condition. With numbers in A2:A10 and the condition stored in D1, =SUMIF(A2:A10,D1) adds the matching numbers in A2:A10. That version is not appropriate when A contains labels and the amounts are in B.

Match an exact x or text that contains x

The criterion "x" means a cell must equal x as its content. It does not mean “find x anywhere inside a longer entry.” For example, it matches a cell containing x, but not a cell containing extra characters such as box or x1. If your labels include additional text and you want to match entries containing x anywhere, use asterisks as wildcards.

With descriptions in A2:A10 and amounts in B2:B10, =SUMIF(A2:A10,"*x*",B2:B10) adds amounts when the description contains x at any position. The asterisk represents any number of characters, including none. Use "x*" when the text must start with x, or "*x" when it must end with x.

Wildcards can make the match broader than intended. For instance, a search for x anywhere will also match words that contain the letter x as part of a longer word. If the data uses distinct labels, exact matching is safer. Excel’s ordinary text matching in SUMIF is not case-sensitive, so x and X are treated as the same criterion.

Sum across a row when the text is in column headings

Sometimes “sum columns where text is x” means that the text appears in a row of headings, and you want to add the values beneath the matching headings. SUMIF can work horizontally as well as vertically: use the heading cells as the criteria range and the corresponding data cells as the sum range.

With headings in C1:J1 and the values to total in C2:J2, use =SUMIF(C1:J1,"x",C2:J2). Excel checks each heading and adds the value directly below it when that heading is x. If C1 and F1 contain x and C2 and F2 contain 10 and 6, the result is 16.

For multiple rows of data, apply the same idea one row at a time, or arrange the data so each record has a label and value on the same row. A vertical list with one condition column and one amount column is often easier to maintain and summarize than a wide layout with repeated headings.

Use SUMIFS when more than one condition applies

If the amount should be included only when the text is x and another condition is also true, use SUMIFS. Unlike SUMIF, SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. It requires all supplied conditions to be met for a row to contribute to the total.

With category labels in A2:A10, regions in B2:B10, and amounts in C2:C10, use =SUMIFS(C2:C10,A2:A10,"x",B2:B10,"West"). This adds amounts only for rows labeled x in column A and West in column B. If the qualifying amounts are 12 and 8, the result is 20. Check the argument order carefully: in SUMIFS, the sum range comes first.

For a single condition, SUMIF is usually the clearer choice. For two or more conditions—such as a label, region, date range, or status—SUMIFS is designed for the job. It can also be easier to extend later when a report gains another condition.

Common problems and how to fix them

The result is zero: Check that the criterion matches the actual cell content and that the sum range contains numbers. A visually similar label can include a leading or trailing space. Try selecting a source cell and checking its contents in the formula bar.
Numbers are stored as text: Values that look like numbers may be stored as text, especially after importing data. Convert them to numeric values if they are not being included as expected. Formatting a text value as a number alone may not convert the underlying value.
The wrong rows are included: Confirm that the criteria range and sum range start and end on corresponding rows. If the first range begins at row 2 but the sum range begins at row 3, the entries no longer line up as intended.
The criterion is in the wrong format: Put a literal text criterion in straight double quotes, as in "x". If the criterion is a cell reference, use the reference without quotation marks around it.
The total changes after adding rows: A fixed range such as A2:A10 will not automatically include data entered below row 10. Extend the ranges or use an Excel Table so its data ranges expand as rows are added.

Also check for hidden rows or filters if you are comparing the result with a visible subtotal. SUMIF evaluates the ranges; it is not a “visible cells only” total. Filtering a list does not change SUMIF into a function that ignores filtered-out records.

Choose ranges that stay useful as data grows

For a short, stable list, explicit ranges such as A2:A100 and B2:B100 are easy to understand. Avoid including a heading row in the criteria range when the heading is not part of the data. Whole-column references can be convenient for a growing list: with labels in column A and amounts in column B, =SUMIF(A:A,"x",B:B) checks the columns as a whole.

Whole-column references are simple, but they can make a workbook slower when used extensively across many formulas, particularly in complex files. A table is often a practical alternative for ongoing data. Select the data and use Insert > Table; formulas that refer to table columns can adjust as table rows are added. Keep column names clear so the criteria and amount fields are easy to distinguish.

Whichever approach you use, keep the labels consistent. A controlled list of categories—using the same spelling and avoiding accidental spaces—makes results more dependable. If the source data is regularly imported, inspect it after each import rather than assuming the visible text and number formatting guarantee clean underlying values.

Frequently Asked Questions

How do I sum a column if another column contains x?
Use SUMIF with the column containing x as the criteria range and the numbers as the sum range. With labels in A2:A10 and values in B2:B10, =SUMIF(A2:A10,"x",B2:B10) adds values on rows where the label is x.

Can SUMIF find x inside a longer text entry?
Yes. Use an asterisk on both sides of x in the criterion to match text containing x anywhere. With descriptions in A2:A10 and amounts in B2:B10, =SUMIF(A2:A10,"*x*",B2:B10) sums amounts for descriptions that contain that character.

Does SUMIF distinguish uppercase X from lowercase x?
No. SUMIF’s text criteria are not case-sensitive, so X and x match the same way. If you need case-sensitive totals, a different formula approach is needed.

How can I sum values for x and another condition?
Use SUMIFS and add a criteria range and criterion for each additional condition. Put the sum range first, then add the range-and-condition pairs in order.

Why does my SUMIF formula return zero?
Common causes include a mismatched criterion, extra spaces in the source text, numbers stored as text, or ranges that do not line up. Check the source cells and verify that both ranges cover the intended rows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.