To add values that match one condition, use SUMIF: with regions in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"East",B2:B6) returns 400 when the East sales are 120, 200, and 80. SUMIF checks a range for a criterion, then adds the corresponding values from a sum range.
What SUMIF Does
SUMIF is useful when you want a conditional total rather than the sum of every number in a column. For example, a sales list can contain several regions, products, or payment statuses. Instead of filtering the list and adding the visible amounts by hand, SUMIF can total the entries that meet one specified condition.
The function has three arguments: the range Excel checks, the criterion that determines which cells qualify, and the range containing the numbers to add. The last argument is optional. If you leave it out, Excel adds the qualifying numbers in the first range. This is handy when the values being tested are also the values being totaled.
SUMIF handles one condition. If a total depends on two or more conditions—such as region and payment status—use SUMIFS instead. The similar names can be confusing: SUMIF puts the sum range last, while SUMIFS puts the sum range first.
Basic SUMIF Example
Suppose A2:A6 contains East, West, East, North, and East, while B2:B6 contains 120, 90, 200, 75, and 80 in the same row order. To total East sales, enter this formula in an empty cell:
=SUMIF(A2:A6,"East",B2:B6)
The result is 400: Excel adds 120, 200, and 80 because those amounts are on the rows where the region is East. It does not add 90 or 75, since their region entries do not match.
The order of the ranges matters. A2:A6 is the range to check, East is the criterion, and B2:B6 is the range to add. Each amount is paired with the region on the same row. If the ranges cover different rows or start in different positions, the result can be wrong or confusing. Keep the criteria range and sum range aligned and normally make them the same size.
Using Numbers, Comparisons, and Cell References
Criteria can select numbers as well as text. For a numeric test, put a comparison operator and its threshold in quotation marks. With sales values in B2:B6 as 120, 90, 200, 75, and 80, this formula totals values greater than 100:
=SUMIF(B2:B6,">100")
The result is 320, because only 120 and 200 are greater than 100. Since the first range also contains the values to add, the optional sum range is omitted. Other useful comparison criteria include ">=" for greater than or equal to, "<" for less than, and "<>" for not equal to. The comparison operator and threshold must be combined as one criterion.
A cell reference makes a criterion easier to change without editing the formula. If D2 contains East and the region and sales data remain in A2:A6 and B2:B6, use:
=SUMIF(A2:A6,D2,B2:B6)
The result is still 400 while D2 contains East. If D2 changes to West, the result updates to 90. For a comparison based on a cell, join the operator in quotation marks to the cell reference with an ampersand. For example, if D2 contains 100, =SUMIF(B2:B6,">"&D2) returns 320. This is different from writing the criterion as the text ">D2", which would look for those literal characters rather than use the value in D2.
Wildcards for Partial Text Matches
For text criteria, SUMIF normally looks for a matching value rather than a substring. Wildcards let you match text patterns: an asterisk represents any number of characters, while a question mark represents one character. For example, with product names in A2:A4 (Apples, Apricots, and Bananas) and sales in B2:B4 (35, 20, and 15), use:
=SUMIF(A2:A4,"Ap*",B2:B4)
The result is 55 because Apples and Apricots begin with Ap. A criterion of "*berry" would match names ending in berry, while "A?e" would match a three-character name beginning with A and ending with e. Wildcards are useful for inconsistent labels, but they can also match more entries than intended, so check the source text and the pattern carefully.
If you need to search for an actual asterisk or question mark rather than use it as a wildcard, precede that character with a tilde in the criterion. For instance, a tilde makes the following wildcard character literal. This is a niche but useful detail when product codes or descriptions contain those symbols.
Dates and SUMIF
Excel stores real dates as numeric serial values, so date criteria can use the same comparison operators as numeric criteria. For a period defined by a start and end date, SUMIFS is often clearer because it can apply both boundaries. Assume A2:A5 contains dates January 2, January 15, February 3, and January 31, 2026, and B2:B5 contains 40, 60, 90, and 25. To total January 2026, use:
=SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1))
The result is 125: the three January amounts are 40, 60, and 25. The end condition is less than February 1, rather than less than or equal to January 31. That approach also includes dates that contain times on January 31, because any time on that date is still earlier than February 1. If date cells are stored as text instead of actual Excel dates, comparisons may not behave as expected; convert the entries to dates before relying on the total.
SUMIF or SUMIFS?
Choose SUMIF when there is one condition, such as one region, one product, or a value above a threshold. Choose SUMIFS when every qualifying row must satisfy multiple conditions. For example, suppose A2:A5 contains East, West, East, East; B2:B5 contains Paid, Paid, Pending, Paid; and C2:C5 contains 100, 50, 80, and 40. To total amounts for East rows whose status is Paid, enter:
=SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Paid")
The result is 140, adding 100 and 40. The East row marked Pending is excluded, as is the West row marked Paid. SUMIFS can take multiple condition-range and criterion pairs, which makes it a natural choice for category-plus-status or date-range-plus-region totals. Remember the argument-order difference: SUMIF starts with the criteria range, whereas SUMIFS starts with the sum range.
Common Problems and How to Fix Them
- Unexpected zero: Check for extra spaces, spelling differences, or numbers stored as text. A cell that looks like East may contain a trailing space, and a text number may not behave like a numeric value.
- Wrong sum range: Confirm that each value in the sum range is on the same row as the criterion it belongs to. Also check that the ranges have matching dimensions.
- Criteria written as text unintentionally: For a comparison, include the operator in the criterion, such as ">100". When the threshold comes from a cell, concatenate the operator and reference with &.
- Partial text matches: A wildcard such as * can include several related labels. Inspect the matching source entries if the total is unexpectedly high.
- Case differences: SUMIF is not case-sensitive, so it does not distinguish East from EAST. If capitalization must determine which rows qualify, SUMIF alone is not the right tool.
- Formula separators: The examples use commas, as in standard English-language Excel syntax. Some regional settings use semicolons as argument separators; if Excel rejects a formula copied from a comma-based example, check the list-separator setting used by your installation.
When debugging, select the formula cell and review the colored range references in the formula bar. You can also check the data directly by sorting or filtering the source table, but remember that SUMIF calculates from the referenced cells whether or not rows are currently hidden by a filter.
Frequently Asked Questions
What is the simplest SUMIF formula?
With categories in A2:A10 and amounts in B2:B10, =SUMIF(A2:A10,"Food",B2:B10) totals the amounts whose category is Food. Replace the ranges and criterion with the locations and label in your own data.
Can SUMIF use a criterion from another cell?
Yes. Put the criterion in a cell and use that reference as the second argument. For instance, if D2 contains Food, =SUMIF(A2:A10,D2,B2:B10) uses the value in D2 and updates if it changes.
Can SUMIF add values that are not next to the criteria range?
Yes. The criteria range and sum range can be in separate columns, as in the examples, as long as corresponding rows align. SUMIF does not require the two ranges to be adjacent.
Does SUMIF distinguish uppercase and lowercase?
No. SUMIF matching is not case-sensitive. Text entries that differ only by capitalization are treated as matches for the same text criterion.
Why does SUMIF return zero when matching rows are present?
Check whether the criterion and source values truly match, including spaces and punctuation. Also verify that the sum-range cells contain numeric values rather than numbers stored as text, and that the formula points to the intended columns and rows.
Can SUMIF add rows that meet two conditions?
Not by itself: SUMIF accepts one criterion. Use SUMIFS for multiple conditions, such as a particular region and a particular status, and put the sum range first in that function.
Related
- CSV File to Excel Converter: Convert Without Data Loss
- Data Cleaning Tools in Excel: What to Use and When
- Data Cleaning in Excel: A Practical Example
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMPRODUCT for Two Columns: Formula and ExamplesTo multiply corresponding values in two Excel columns and add the products, use =SUMPRODUCT(A2:A5,B2:B5). If A2:A5 contains 2,…
- Excel INDIRECT Sheet Name: Build Dynamic Sheet ReferencesTo use a cell’s text as a worksheet name in Excel, combine it with the cell or range…
- VLOOKUP in Excel: Purpose, Examples, and Limits ExplainedVLOOKUP in Excel looks for a value in the first column of a range and returns a related…
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
- Show Formulas in Excel: Steps for Windows, Mac, and WebTo show formulas instead of calculated results in Excel, open the Formulas tab and select Show Formulas in…
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.