To total values in an Excel column when another column meets one condition, use SUMIF. With categories in A2:A6 and amounts in B2:B6, =SUMIF(A2:A6,"East",B2:B6) adds the amounts for East; if the East amounts are 120, 80, and 50, the result is 250.
What SUMIF does
SUMIF adds numbers that match a single criterion. It is useful when a list has one row per transaction, order, or item and you want a conditional total—for example, sales for one region, expenses in one category, or units for a particular product. The criterion is checked in one range, and Excel adds the corresponding values from a sum range.
The function follows this pattern: SUMIF(range, criteria, [sum_range]). The square brackets in the syntax indicate that sum_range is optional; they are not typed into the formula. range contains the cells Excel evaluates, criteria tells Excel what to match, and sum_range contains the values to add. In the opening example, Excel checks the category in column A and adds the associated amount in column B.
If you leave out sum_range, Excel adds qualifying cells from range itself. This is handy when the numbers being tested are also the numbers to total, but it is not the usual setup when one column contains labels and another contains amounts.
Build a basic column-total formula
For a straightforward total by label, keep the criteria range and sum range aligned row by row. Suppose A2:A6 contains East, West, East, North, East, while B2:B6 contains 120, 90, 80, 110, 50. To total East, enter the formula in any empty cell:
With categories in A2:A6 and amounts in B2:B6, =SUMIF(A2:A6,"East",B2:B6) returns 250 (120 + 80 + 50).
To enter it in Microsoft Excel for Microsoft 365 on Windows:
- Select the cell where you want the result.
- Type the formula, replacing the example ranges and criterion with your own.
- Press Enter. If the source list changes, Excel recalculates the total.
On a Mac, the formula works the same way; press Return to confirm it. Excel for the web also supports SUMIF. A workbook’s regional settings can affect the argument separator: the examples here use commas, while some installations display semicolons. If Excel rejects a correctly structured formula, check the separator used by your installation.
Choose the right range references
The two ranges should describe corresponding rows. If the labels are in A2:A100 and the values are in B2:B100, use those same row boundaries for both. Avoid starting one range on row 2 and the other on row 3, because Excel would pair the wrong labels and values. Keeping the ranges the same size and shape makes the formula easier to check and maintain.
You can use whole-column references when your list will grow. With categories in column A and amounts in column B, =SUMIF(A:A,"East",B:B) checks the category column and totals the matching entries in the amount column. This approach automatically includes new rows. For a modest workbook it is convenient, but a very large workbook with many full-column calculations may recalculate more work than necessary. Bounded ranges or an Excel Table can be more efficient and easier to manage.
For a growing dataset, consider converting the list to a table using Insert > Table. Table formulas can use descriptive column names and typically expand as rows are added. The exact table formula depends on the table and header names, so confirm those names in your workbook rather than copying a formula that assumes different headings.
Use text, numbers, and cell references as criteria
A criterion can be a fixed value, a comparison, or a reference to another cell. Text criteria are enclosed in straight double quotation marks when typed directly into a formula. A criterion stored in a cell is referenced without quotation marks. This makes a result selector easy to change without editing the formula.
With categories in A2:A6, amounts in B2:B6, and the word East in D2, =SUMIF(A2:A6,D2,B2:B6) returns 250. Change D2 to West and the formula returns 90. This is useful for a small report where a user selects the category to display.
Comparison operators include greater than, less than, and not equal to. When the operator and value are typed directly, put the comparison expression in quotation marks. With numbers 120, 90, 80, 110, and 50 in A2:A6, =SUMIF(A2:A6,">100") returns 230 (120 + 110). Because the sum range is omitted, Excel evaluates and adds the qualifying values in A2:A6.
If the threshold is in a cell, join the operator to the cell reference with the ampersand. With amounts in A2:A6 and a threshold of 100 in D2, =SUMIF(A2:A6,">"&D2) returns 230. This construction is useful for adjustable thresholds; writing ">D2" instead would search for text that literally includes D2 rather than using the value in that cell.
Match partial text and date ranges
For text criteria, * stands for any number of characters and ? stands for one character. Use ~ before a wildcard character if you need to match a literal asterisk or question mark. For instance, with product codes in A2:A5—AB-10, AB-20, CD-10, and ABX-30—and amounts in B2:B5—40, 60, 25, and 30—=SUMIF(A2:A5,"AB-*",B2:B5) returns 100. The pattern matches codes that begin with AB-.
SUMIF text matching is not case-sensitive, so East and EAST are treated as the same criterion. If case-sensitive matching is essential, SUMIF alone is not designed to distinguish those spellings; a different formula approach is needed.
Dates are stored as numeric values in Excel, so criteria can use comparison operators. It is safer to construct date criteria with DATE than to type a date string whose interpretation might depend on regional settings. With dates in A2:A6 (December 31, 2025; January 1, 2026; January 10, 2026; February 4, 2026; February 20, 2026) and amounts in B2:B6 (10, 20, 30, 40, 50), =SUMIF(A2:A6,">="&DATE(2026,1,1),B2:B6) returns 140. It includes the four dates on or after January 1, 2026.
To total a bounded period, use two conditions instead of trying to combine them in one SUMIF criterion. SUMIFS, covered below, can set both the start and end date. If the source dates include times, use a start-inclusive and next-day-exclusive boundary when defining a single day; a date entered at midnight will not include later times on that date in a simple equality match.
Use SUMIFS when the total needs more conditions
SUMIF handles one criterion. For multiple requirements, use SUMIFS, whose sum range comes first, followed by pairs of criteria ranges and criteria. For example, it can total sales for East in January, or expenses for one department that exceed a threshold.
With regions in A2:A5 (East, East, West, East), dates in B2:B5 (January 5, January 18, January 12, February 2, 2026), and sales in C2:C5 (100, 150, 200, 250), =SUMIFS(C2:C5,A2:A5,"East",B2:B5,">="&DATE(2026,1,1),B2:B5,"<"&DATE(2026,2,1)) returns 250. It adds the two East sales dated in January, 100 + 150. SUMIFS is generally clearer than stacking several single-condition formulas when every condition must be true for a row to count.
Use SUMIF when the question is “total this column for one matching condition.” Use SUMIFS when it is “total this column when these several conditions are all met.” The functions are related, but their argument order differs: SUMIF starts with the criteria range, while SUMIFS starts with the sum range. Checking that order is a useful way to catch a copied formula error.
Common reasons a SUMIF total is wrong
A zero or unexpectedly small result often comes from a mismatch between the criterion and the source data. Check for leading or trailing spaces, inconsistent labels, or numbers stored as text. A cell that looks like the number 100 may not behave like a numeric 100 if it was imported as text. Also verify that the formula points to the intended columns and that the matching rows fall within the selected ranges.
- Wrong range alignment: Make sure each criterion row corresponds to the sum value on that same row, and use ranges of the same size.
- Criterion written incorrectly: Put text and typed comparisons in quotes. Join a comparison operator to a cell reference with
&. - Dates treated as text: Confirm that Excel recognizes the source entries as dates. Imported date-looking strings may need conversion before date comparisons work as intended.
- Unexpected wildcard matching: A criterion such as
"East*"can match more labels than an exact criterion. Use a fixed label if partial matching is not wanted. - Formula not recalculating: Check Formulas > Calculation Options and confirm that Automatic is selected if results are not updating.
When checking a result, filter the source list to the same criterion and add the visible amounts independently. This simple audit can reveal a bad range, an unexpected match, or a value stored as text. If the formula returns an error rather than a wrong total, inspect each referenced range for errors and verify that the criteria and sum ranges refer to valid cells.
Frequently Asked Questions
How do I total an entire Excel column with SUMIF?
Use a full-column criteria reference and a full-column sum reference, such as =SUMIF(A:A,"East",B:B), when labels are in A and amounts are in B. Full columns expand automatically as rows are added. If a workbook has many formulas or is slow, use bounded ranges or a table instead.
Can SUMIF add values based on a criterion in another cell?
Yes. Put the criterion in a cell and use that cell reference as the second argument. For example, with categories in A2:A6, amounts in B2:B6, and the desired category in D2, =SUMIF(A2:A6,D2,B2:B6) totals the category selected in D2.
How do I use SUMIF for greater than a number?
Write the comparison as a quoted criterion, such as ">100", when the threshold is fixed. If the threshold is in D2, join the operator to that reference, as in ">"&D2. The comparison is applied to the criteria range.
Does SUMIF distinguish uppercase and lowercase text?
No. SUMIF text criteria are not case-sensitive, so differently capitalized versions of the same text match. Use another formula method if capitalization must determine which rows are included.
Why does SUMIF return zero when matching values are visible?
Check for extra spaces, text-versus-number differences, date values stored as text, and an incorrect range or criterion. A visible label can also contain characters that are hard to notice. Testing a few source cells or filtering the list by the intended criterion can help identify the mismatch.
Choosing a reliable approach
For one condition, a clear SUMIF formula is usually the simplest answer: identify the cells to check, state the criterion, and point to the values to add. Use a cell reference for criteria that need to change, wildcards for deliberate partial text matches, and DATE-based criteria for date comparisons. When more than one condition must be met, switch to SUMIFS rather than making the single-condition formula harder to interpret.
Before relying on a total in a report, verify the source ranges, confirm that the labels and numbers are stored consistently, and compare a sample result with the underlying rows. These habits make a SUMIF column total easier to update and help prevent a plausible-looking but incorrect answer.
Related
- Excel Data Run Analysis: A Practical Runs Test
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Expansion: AutoCorrect, Formulas, and MoreExcel has no single feature called “text expansion,” but you can make a short abbreviation expand as you…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
- HLOOKUP With Multiple Criteria: Formulas and ExamplesHLOOKUP does not accept separate criteria arguments, but you can search by multiple criteria by joining the criteria…
- Excel MAX of Filtered Data: Formula, Limits, and FixesIf your numbers are in B2:B100, use =SUBTOTAL(4,B2:B100) to return the largest value among rows currently shown by…
- Excel Days Until Due: Formula, Date Math, and Alerts ExplainedTo calculate days until a due date in Excel, put the due date in A2 and enter =A2-TODAY()…
- SUMIFS Excel Expression: Syntax and ExamplesThe SUMIFS Excel expression adds numbers that meet multiple conditions. Its basic form is =SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee"): this adds values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.