Excel SUM Based on Another Column: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5 and amounts in B2:B5, enter =SUMIF(A2:A5,"East",B2:B5) to total the East amounts. If the East rows contain 120, 80, and 150, the result is 350. Use SUMIFS when the total must meet more than one condition.

How SUMIF matches rows and adds values

SUMIF checks each cell in a criteria range against a condition. When a row meets that condition, Excel adds the corresponding cell from the sum range. The two ranges therefore need to line up: A2 is paired with B2, A3 with B3, and so on. In the opening example, the category is in column A and the amount to add is in column B.

The function’s argument order is SUMIF(range, criteria, [sum_range]). The square brackets in Excel’s function help indicate that the last argument is optional; they are not typed into the formula. If you leave out sum_range, Excel adds cells in range itself. That can be useful when the numbers you want to add are also the numbers being tested, but it is not the usual setup for adding one column based on another.

SUMIF evaluates the condition separately for each row, then returns one total. It does not rearrange, filter, or change the source data. This makes it suitable for questions such as “What are all sales for the West region?” or “How many expenses belong to Travel?” as long as the condition and amounts are stored in aligned rows.

Build a basic SUMIF formula

Start by identifying the column to check, the condition to match, and the numeric column to add. For example, suppose A2:A5 contains East, West, East, and North, while B2:B5 contains 120, 90, 80, and 150. To add only the two East amounts, use the opening formula: 120 + 80 = 200. The 150 in the last row is not included because its category is North.

When entering a formula, you can type the ranges or select them with the mouse. A practical sequence is:

  1. Click the cell where you want the result.
  2. Type =SUMIF(, then select the category cells.
  3. Type a comma and enter the condition, such as "East".
  4. Type another comma and select the amount cells.
  5. Type ) and press Enter.

On Windows, Enter confirms the formula. On Mac, Enter or Return normally confirms it, depending on the keyboard. If your Excel settings use semicolons as formula separators rather than commas, use the separator Excel expects; this is a regional setting, not a different SUMIF function.

Use a cell as the condition

A condition does not have to be written inside the formula. Putting it in a worksheet cell makes the total easy to change and is especially useful for reports. For example, place East in D2 and refer to D2 as the criterion. With categories in A2:A5, amounts in B2:B5, and East in D2, enter =SUMIF(A2:A5,D2,B2:B5). The result is 200 for the example data above. Change D2 to West and the result becomes 90 without editing the formula.

Cell references also help avoid spelling inconsistencies. If a category is selected from a dropdown list, for instance, the criterion can match the stored category exactly rather than relying on someone to type it the same way each time. To keep the same criterion cell when copying a formula, use an absolute reference. With the same data and criterion in D2, =SUMIF(A2:A5,$D$2,B2:B5) also returns 200; the dollar signs lock D2 when the formula is copied. The ranges in this small example remain fixed as written.

Sum using multiple conditions with SUMIFS

Use SUMIFS when a row must satisfy two or more conditions. Its argument order differs from SUMIF: the sum range comes first, followed by a criteria range and criterion for each condition. For instance, imagine A2:A5 contains regions, B2:B5 contains product names, and C2:C5 contains sales amounts. The rows are East, Tea, 120; East, Coffee, 80; West, Tea, 90; and East, Tea, 150. To total East sales for Tea, enter =SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Tea"). It returns 270, adding 120 and 150.

Each criteria pair adds another requirement, and all requirements must be true for a row to count. With region in D2 and product in E2, the same layout can be controlled from cells: =SUMIFS(C2:C5,A2:A5,D2,B2:B5,E2). If D2 is East and E2 is Tea, the result is 270. This pattern works well for dashboards where users choose a region, product, department, or other filter value.

SUMIFS is not simply SUMIF with a different name: remember that SUMIF puts its criteria range first, while SUMIFS starts with the sum range. Mixing up the order is a common cause of incorrect totals. In current Microsoft 365 Excel, both functions are available.

Use number and date conditions

Criteria can test numbers as well as text. Comparison operators such as greater than, less than, and not equal to are written inside quotation marks when typed as literal criteria. If the operator is combined with a value in another cell, join the operator and cell reference with an ampersand.

With categories in A2:A5 and amounts in B2:B5 containing 120, 90, 80, and 150, this formula totals amounts greater than 100: =SUMIF(B2:B5,">100",B2:B5). Its result is 270, from 120 and 150. Here the amount column is both the criteria range and sum range. To compare values against a threshold stored in D2, use =SUMIF(B2:B5,">"&D2,B2:B5); if D2 is 100, it also returns 270.

Dates are stored as numbers in Excel, so SUMIFS can use date boundaries. Suppose A2:A5 contains transaction dates of January 5, January 20, February 3, and February 18, 2026, and B2:B5 contains 40, 60, 75, and 25. To sum January only, enter =SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1)). It returns 100. The inclusive start and exclusive start of the next month include every January date, including dates that may contain times. DATE constructs the boundary dates without depending on how a date is displayed.

Match text that contains a word

For partial text matching, SUMIF and SUMIFS accept the asterisk wildcard for any number of characters and the question mark wildcard for one character. For example, a criterion of "*East*" matches cells containing East anywhere, such as East Coast or Northeast. With labels in A2:A4 reading East Coast, Northeast, and West, and amounts in B2:B4 of 100, 50, and 70, =SUMIF(A2:A4,"*East*",B2:B4) returns 150.

Wildcards are useful when labels include extra wording, but they can also match more rows than intended. If you only want the exact category East, use "East" rather than a wildcard. SUMIF text matching is not case-sensitive, so East and EAST are treated as the same criterion. If a label contains a literal asterisk or question mark, use a tilde before the character to treat it as text rather than a wildcard.

Use an Excel Table for expanding data

Ordinary ranges such as A2:A100 cover only the rows specified. If new records are added below row 100, the formula will not include them unless the ranges are extended. An Excel Table can make a growing dataset easier to manage because its structured references expand as table rows are added.

To create one in Excel for Microsoft 365 on Windows, select the data and choose Insert > Table, confirm whether the table has headers, and select OK. On Mac, the Insert tab also offers Table. Suppose the table is named Sales and has columns Region and Amount. With those table columns in place, enter =SUMIF(Sales[Region],"East",Sales[Amount]) in a cell outside the table. It totals the Amount values for rows whose Region is East. If the table contains East amounts of 120 and 80, the result is 200. Excel commonly names a new table Table1; you can change its name on the Table Design tab.

Table references are readable because they use column headings rather than cell coordinates. They can also reduce range-size mistakes as the list grows. Check that headings are unique and that the formula is outside the table if you want a single summary total rather than a calculated column repeating a result on every row.

Choose the right method and troubleshoot totals

For one condition, SUMIF is usually the clearest option. For two or more simultaneous conditions, use SUMIFS. If you need a visible list of matching records as well as a total, filtering the data may be more informative; SUMIF returns a number, not the matching rows. A PivotTable is another useful option when you want to summarize many categories and explore totals interactively. These methods answer related questions, but they are not interchangeable: a formula updates in its result cell, while a PivotTable organizes a summarized view.

If a result is zero or unexpectedly small, check these common causes:

  • Misaligned ranges: Make sure the criteria and sum ranges start and end on the same rows. SUMIF ranges of different sizes can produce confusing results.
  • Extra spaces or inconsistent labels: “East” and “East ” may not match. TRIM can help clean imported text, but it should be applied to the source data or a helper column rather than assumed to happen automatically inside SUMIF.
  • Numbers stored as text: A value that looks numeric may not be recognized as a number to add. Check the source cells and convert imported text values to numbers where appropriate.
  • Wrong criteria syntax: A comparison criterion such as greater than 100 needs the operator and value together as text, while a cell-based threshold needs concatenation with &.
  • Dates stored as text: A displayed date may be text rather than an Excel date value. Confirm the source values are real dates if date conditions return an unexpected total.
  • Formula not recalculated: Excel normally calculates automatically, but a workbook can be set to manual calculation. Check Formulas > Calculation Options if results do not update after changing data.

Also verify that the criterion is actually present in the criteria range and that the sum range contains the amounts you intend to include. Selecting full columns can be convenient, but bounded ranges or tables make the intended data area clearer and can help avoid including unrelated values above or below a dataset.

Frequently Asked Questions

Can Excel sum a column based on text in another column?
Yes. Use SUMIF for one text condition, with the text column as the criteria range and the numeric column as the sum range. Use SUMIFS if the row must also meet other conditions.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and places the criteria range before the criterion. SUMIFS handles multiple conditions and places the sum range first, followed by criteria-range and criterion pairs. Pay attention to that argument-order difference.

Can I use a cell reference instead of typing the category into the formula?
Yes. Put the category in a cell and use that cell as the criterion, such as D2. This makes it easy to change the selected category without rewriting the formula.

How do I sum values for a date range?
Use SUMIFS with the date column as a criteria range twice: once for the start boundary and once for the end boundary. Using a start date that is greater than or equal to the first day and an end date less than the next period includes the intended dates cleanly.

Why does SUMIF return zero when matching text looks identical?
Check for trailing or leading spaces, different spelling, text imported with hidden characters, or a criterion that is not in the selected range. Also confirm that the sum-range cells contain actual numbers rather than numeric text.

Related

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.