To sum values in Excel when one condition is true, use SUMIFS with one criteria range and one criterion. With regions in B2:B8 and sales amounts in C2:C8, this formula adds the sales for North: =SUMIFS(C2:C8,B2:B8,"North"). You can also use SUMIF for a single condition; SUMIFS is useful when you may later add more conditions.
What SUMIFS Means With One Criterion
SUMIFS adds numbers from a sum range only when corresponding cells in one or more criteria ranges meet the conditions you specify. Its name can make it sound as though it requires several conditions, but it works with just one. In that case, the function has one criteria-range-and-criterion pair after the sum range.
The order matters: SUMIFS puts the range to add first, followed by the range to check and the condition to apply. With the North example, Excel checks each cell in B2:B8. For each row where the region is North, it adds the corresponding value from C2:C8. If those North values are 120, 85, and 210, the result is 415.
This is useful for questions such as total sales for one salesperson, total expenses assigned to one category, or total units sold for one product. It does not change or filter the underlying data; it returns a calculated total in the cell containing the formula.
SUMIFS Syntax and Argument Order
The general syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Square brackets in Excel’s function documentation indicate optional arguments; do not type those brackets into a formula. For one criterion, provide the first three arguments only.
- sum_range is the cells containing the numbers you want to add.
- criteria_range1 is the cells Excel checks.
- criteria1 is the condition those cells must meet. It can be text, a number, a comparison expression, a cell reference, or a wildcard pattern.
For example, with category names in A2:A30 and costs in D2:D30, the formula would check each category and sum the corresponding cost when it matches the chosen category. SUMIFS uses the same argument order even when there is only one condition. This differs from SUMIF, whose argument order places the criteria range first and the sum range last.
Keep the ranges the same size and shape. A sum range with 29 rows should be paired with a criteria range covering the corresponding 29 rows. Using ranges that start or end on different rows can produce incorrect totals or an error. For clarity, use matching endpoints such as A2:A30 and D2:D30 rather than whole-column references mixed with partial ranges.
A Worked Example With One Condition
Suppose a small sales table has the following amounts: North has 120 and 85, South has 90 and 140, and North has another 210. The total for North should be 120 + 85 + 210 = 415. With regions in B2:B6 and sales amounts in C2:C6, enter this formula in an empty cell:
=SUMIFS(C2:C6,B2:B6,"North")
The result is 415. Excel compares each region with the text criterion North and adds the sales value on the same row when it matches. The South entries are ignored. Text criteria are written inside straight double quotation marks when typed directly into a formula.
If the region to total is entered in E2 instead, the formula can refer to that cell. With regions in B2:B6, sales in C2:C6, and the requested region in E2, use:
=SUMIFS(C2:C6,B2:B6,E2)
If E2 contains North, the result is still 415. A cell reference makes the calculation easier to reuse: change E2 to South and the result updates to 230. The formula itself does not need editing.
Text, Numbers, Comparisons, and Wildcards
A single criterion does not have to be an exact word. SUMIFS can test for exact text, a number, or values that satisfy a comparison operator. When you type text or an operator-based condition directly, put the criterion in quotation marks. Common operators include greater than (>), less than (<), equal to (=), and not equal to (<>).
For instance, with product names in A2:A10 and units in C2:C10, you could total units for a product by using its name as the criterion. To total units for rows where the value in C2:C10 is at least 100, use the units range as both the sum range and the criteria range. With units in C2:C10, the formula is:
=SUMIFS(C2:C10,C2:C10,">=100")
This adds every value of 100 or more, giving 100 or greater values an exact test rather than relying on visual sorting. If the threshold is stored in E2, with values in C2:C10, join the operator to the cell reference like this:
=SUMIFS(C2:C10,C2:C10,">="&E2)
For text patterns, the asterisk (*) matches any number of characters, while the question mark (?) matches one character. With descriptions in A2:A20 and amounts in B2:B20, this sums rows whose description begins with the letters Ship:
=SUMIFS(B2:B20,A2:A20,"Ship*")
Wildcards are handy when entries include changing details such as a suffix or code. To search for a literal asterisk or question mark, precede the wildcard character with a tilde (~). SUMIFS text matching is not case-sensitive, so North and NORTH are treated as the same criterion. If capitalization must distinguish categories, SUMIFS alone is not a case-sensitive solution.
SUMIFS or SUMIF for One Condition?
Both functions can sum values based on one condition, so either can be appropriate. SUMIF is designed for a single condition and uses the syntax SUMIF(range, criteria, [sum_range]). SUMIFS supports multiple conditions and puts the sum range first. This difference in argument order is a common source of mistakes when moving between the functions.
With regions in B2:B8 and sales amounts in C2:C8, the SUMIF version for North is:
=SUMIF(B2:B8,"North",C2:C8)
For the same layout, the SUMIFS version is:
=SUMIFS(C2:C8,B2:B8,"North")
Both return the same total when the ranges and criterion are equivalent. Choose SUMIF if you want the shorter, single-condition function and do not expect to add another test. Choose SUMIFS if you want a consistent pattern for formulas that may grow to include additional conditions. Do not add an empty criterion argument just to make SUMIFS look more like SUMIF; provide a real criteria range and criterion pair for each condition.
Common Problems and How to Fix Them
A total of zero does not necessarily mean the formula is broken. It can mean no cells matched the criterion, or matching rows contain zeros or blank cells in the sum range. Check spelling, leading or trailing spaces, the selected ranges, and whether the expected amounts are stored as numbers rather than text. A copied value can look numeric while still being text, in which case it may not add as expected.
- Wrong argument order: In SUMIFS, the sum range comes first. Confirm that the first range contains the numbers you intend to total.
- Range mismatch: Make sure the criteria and sum ranges cover corresponding rows and have equal dimensions.
- Criteria punctuation: Put a typed text criterion such as North in straight quotation marks. For a comparison based on a cell, join the operator and reference with an ampersand.
- Dates and times: Excel stores dates as serial values. A cell that displays a date may also include a time component, so an exact date criterion may fail to match values that include times. Check the underlying values and use a suitable date boundary test if needed.
- Hidden rows: SUMIFS includes matching rows even if they are hidden by a filter. It is not a visible-rows-only subtotal function.
When a formula gives an unexpected result, test the condition separately by checking the source rows and confirming which ones should qualify. Also verify that the formula is pointing at the intended sheet and cells. If you copy it down a column, consider whether references should move or be fixed with dollar signs; for example, a lookup cell that must stay in E2 can be written as $E$2.
Using SUMIFS in a Reusable Worksheet
A single-condition total is often part of a summary area. A simple arrangement is to list categories in one column and put a total formula beside each category. Set up the first formula with the category cell as its criterion, then fill it down. Use relative references for the data ranges only when they should shift; for a summary copied down, absolute references can keep the source ranges fixed.
For example, with categories in B2:B100, amounts in C2:C100, and a summary category in E2, this formula fixes the source ranges while allowing the summary criterion to change as it is copied down:
=SUMIFS($C$2:$C$100,$B$2:$B$100,E2)
If E2 contains North and the matching amounts in C2:C100 total 415, the formula returns 415. When copied to the next row, E2 becomes E3, but the source data ranges remain unchanged. This is useful for a compact report without manually editing each formula.
For a long-lived workbook, keep source data in consistent columns, use a header row, and avoid mixing labels or notes into the data ranges. If new records are regularly appended, an Excel Table can make formulas easier to maintain because table ranges expand as rows are added. The exact structured-reference formula depends on the table and column names, so use the names actually present in your workbook.
Frequently Asked Questions
Can SUMIFS have only one criterion?
Yes. Provide the sum range, one criteria range, and one criterion. Additional criteria-range-and-criterion pairs are optional.
Is SUMIFS better than SUMIF for one condition?
Neither is universally better. SUMIF has a compact syntax for one condition; SUMIFS uses a consistent sum-range-first order and is easier to extend with additional conditions.
Why does my SUMIFS formula return zero?
Usually, no rows match exactly, the ranges do not line up, the criterion contains a typo or extra space, or the values to add are not numeric. Check the source cells and confirm the formula’s range order.
Can I use a cell reference as the criterion?
Yes. Put the reference in the criterion argument without quotation marks. For a comparison such as greater than a threshold in a cell, combine the operator in quotes with the cell reference using &.
Does SUMIFS distinguish uppercase and lowercase text?
No. Its text criteria are not case-sensitive. Values that differ only by letter case match the same text criterion.
Does SUMIFS ignore filtered or hidden rows?
No. It evaluates qualifying rows whether they are visible or hidden. Use a method designed for visible-only totals if that is the requirement.
Related
- CAGR Return in Excel: Calculate Investment Growth
- How to Calculate CAGR in Excel for 3 Years
- How to Calculate Row Percentage in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Month and YearIf the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such…
- Excel Present Value Formula: PV Function and ExamplesIn Excel, use the PV function to calculate the present value of future cash flows: it discounts them…
- Excel MATCH Arguments: What Each One DoesExcel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and…
- VLOOKUP Excel Grading: Match Scores to Letter GradesTo assign a letter grade from a numeric score in Excel, put the grade cutoffs in ascending order…
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text…
- PMT Function Syntax in Excel: Arguments and ExamplesIn Excel, the PMT function syntax is =PMT(rate, nper, pv, fv, type). The first three arguments are required;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.