To sum values in an Excel column only when another column meets a condition, use SUMIFS: =SUMIFS(D2:D8,A2:A8,"North"). Here, D2:D8 contains the numbers to add and A2:A8 contains the region names; the formula returns the total for rows where the region is North.
SUMIFS can check one or several columns, so it works for questions such as “What were North’s hardware sales?” or “How much was sold after a certain date?” The key is to match each criteria range to the data rows you want included, then write criteria in the form Excel expects.
What SUMIFS column criteria means
A column criterion is a rule Excel applies to values in a column. It might require an exact text match, such as North, a number above 100, or a date within a chosen month. SUMIFS evaluates that rule for each row and adds the corresponding value from the sum range only when the row meets every criterion.
The function syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The sum range comes first, followed by a range to test and its criterion. You can add more range-and-criterion pairs as needed. Multiple criteria are combined with AND logic: a row must pass all the tests to contribute to the total.
SUMIFS is useful when data is arranged in rows, with fields such as region, product, date, and sales in separate columns. The function does not require the criteria column to be next to the values being added. It only requires the ranges to line up row by row.
How SUMIFS evaluates ranges
Think of SUMIFS as scanning down the worksheet one row at a time. It checks the value in each criteria range against its paired criterion. If all checks are true for a row, Excel adds that row’s value from the sum range. If any check fails, that value is skipped.
For reliable results, the sum range and every criteria range should cover the same number of rows and align to the same records. For example, pairing D2:D100 with A3:A101 shifts the tests by one row: Excel would compare a region in row 3 with a sales value in row 2. Excel may accept ranges of different sizes in some situations, but mismatched ranges can produce confusing or unintended results, so keep their dimensions consistent.
SUMIFS is different from SUMIF mainly in argument order. SUMIF takes its criteria range first and has one criterion; SUMIFS puts the sum range first and supports multiple criteria. Remembering that difference prevents a common formula error when switching between the two functions.
Basic example using a criteria column
Suppose a worksheet has Region in A, Product in B, Status in C, and Sales in D. Rows 2 through 8 contain these records: North/Hardware/Paid/120, South/Hardware/Paid/80, North/Software/Paid/150, East/Hardware/Paid/200, North/Hardware/Pending/90, South/Software/Paid/110, and North/Hardware/Paid/70.
With regions in A2:A8 and sales in D2:D8, use this formula to total North sales. It returns 430: 120 + 150 + 90 + 70.
=SUMIFS(D2:D8,A2:A8,"North")
To make the condition easy to change, put North in F2. With sales in D2:D8, regions in A2:A8, and the chosen region in F2, use the following formula; it also returns 430 while F2 contains North.
=SUMIFS(D2:D8,A2:A8,F2)
A cell reference is handy for reports where someone may choose a region from a dropdown or type a different value. The formula stays the same while the result updates when the criterion cell changes. If the criterion cell is empty, do not assume that it means “ignore this test”; an empty criterion can match blank cells. For optional filters, use a separate formula design or make the criterion explicit.
Adding criteria from more columns
To total Hardware sales in North, both the Region column and Product column must match. With regions in A2:A8, products in B2:B8, and sales in D2:D8, the formula below returns 280: 120 + 90 + 70. It includes the Pending row because this version does not test Status.
=SUMIFS(D2:D8,A2:A8,"North",B2:B8,"Hardware")
To include only Paid hardware sales in North, add Status in C as a third test. With the same rows and columns, this returns 190: 120 + 70.
=SUMIFS(D2:D8,A2:A8,"North",B2:B8,"Hardware",C2:C8,"Paid")
Each added condition needs its own criteria-range and criterion pair. The order of those pairs does not change the logical result, provided each criterion remains beside the correct range. This is useful for layered reports, but more criteria also make it worth labeling input cells clearly so a later editor can tell which condition each one controls.
Using comparison operators and wildcards
For numeric tests, put an operator and number in a text criterion. With sales in D2:D8, the formula below sums sales greater than 100. It returns 580: 120 + 150 + 200 + 110.
=SUMIFS(D2:D8,D2:D8,">100")
Common operators are > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), and <> (not equal to). For a condition stored in a cell, join the operator to the cell reference with the ampersand. With sales in D2:D8 and a cutoff in F2 containing 100, this formula returns 580 because it tests for values greater than F2.
=SUMIFS(D2:D8,D2:D8,">"&F2)
Text criteria can use wildcards. An asterisk (*) represents any number of characters, while a question mark (?) represents one character. With product names in B2:B8 and sales in D2:D8, this formula returns 820 because every product in the example ends in ware, so each matches *ware.
=SUMIFS(D2:D8,B2:B8,"*ware")
If a product name contains a literal asterisk or question mark, precede that wildcard character with a tilde (~) in the criterion to search for it literally. Wildcards are useful for partial matches, but a broad pattern can include more rows than expected. Check the matching records when a wildcard total seems unexpectedly high.
Applying criteria to dates
Excel stores dates as serial numbers, so date criteria are comparisons against numeric values. A robust way to sum one month is to use an inclusive first-day boundary and an exclusive first day of the next month. This also handles times stored alongside dates, because a timestamp later on the last day remains less than the next month’s starting point.
Suppose A2:A5 contains January 5, January 15, February 1, and January 31, 2026, while B2:B5 contains 100, 240, 90, and 60. With dates in A2:A5 and amounts in B2:B5, the formula below totals January 2026 and returns 400: 100 + 240 + 60.
=SUMIFS(B2:B5,A2:A5,">="&DATE(2026,1,1),A2:A5,"<"&DATE(2026,2,1))
DATE constructs a genuine Excel date from year, month, and day values. When dates come from input cells instead, make sure the cells contain real Excel dates rather than text that only looks like a date. Regional date formats can make typed date strings ambiguous, which is one reason date boundaries built with DATE are dependable.
Whole-column references and Excel Tables
You can use entire columns in a SUMIFS formula. With regions in column A and sales in column D, this formula sums North sales from the full columns:
=SUMIFS(D:D,A:A,"North")
Whole-column references are convenient when records will be added later, and text headings in the columns are normally ignored when Excel tries to add the sum range. However, formulas that repeatedly scan many full columns across a large workbook can add calculation work. Bounded ranges or an Excel Table may be a better fit for a large or complex model.
For a Table named SalesData with columns Sales and Region, the equivalent structured-reference formula is =SUMIFS(SalesData[Sales],SalesData[Region],"North"). Table formulas expand as rows are added, and column names make the formula easier to interpret than letter references. To create a Table in Excel for Microsoft 365 on Windows, select the data and choose Insert > Table, then confirm whether the table has headers. Excel for Mac has the same Insert > Table command; the exact dialog appearance may differ. Structured references are especially helpful when columns move, since the formula refers to their names rather than fixed letters.
Common errors and how to diagnose them
A zero result does not always mean there are no matching records. It can mean the criterion differs from the stored values, a range is misaligned, or the values being added are stored as text. Check a few matching rows and inspect the underlying cells before changing the formula.
- Text that looks identical: A trailing space in a region name, such as North followed by a space, is a different text value. Imported data can contain such spaces or nonprinting characters.
- Numbers stored as text: A sales value displayed as 120 may be text, especially after a CSV import. Convert the source values to numbers if they are meant to be summed.
- Wrong criterion syntax: A comparison operator must be part of the criterion, such as ">100". When the cutoff is in a cell, join the operator and cell reference, as in ">"&F2.
- Range misalignment: Confirm that the sum range and every criteria range start and end on the same rows.
- Unexpected date result: Check that the source contains real dates and whether cells also contain times. An exclusive next-month boundary avoids dropping times on the last day.
- Formula separators: The examples use commas, as in standard English-language Excel syntax. Some regional settings use semicolons between arguments; if Excel rejects a formula, check the list separator configured for that installation.
For a targeted check, temporarily filter the data to the criteria values and compare the visible records with the sum. In Excel for Microsoft 365 on Windows, use Data > Filter; Excel for Mac also provides Filter on the Data tab. Filtering does not change the SUMIFS calculation, but it can reveal misspellings, unexpected blanks, and rows that a wildcard is including.
SUMIFS versus other ways to total data
SUMIFS is a good choice when the task is to add numbers that meet one or more conditions. Use SUMIF when a single condition is enough and you prefer its syntax, or keep SUMIFS for consistency if other report formulas have multiple tests. The functions return a sum; they do not list matching rows or count them.
If the goal is to count records rather than add a numeric column, COUNTIFS is the related function for multiple criteria. If the goal is to view matching records, filtering or a dynamic-array formula such as FILTER may be more appropriate. FILTER is available in Microsoft 365 and Excel 2021 or later; it returns matching values rather than a conditional total. These tools solve related but different problems, so choose according to whether the required output is a total, a count, or a set of rows.
For an OR condition, SUMIFS does not treat two criteria in the same range as alternatives; separate SUMIFS calculations can be added. With regions in A2:A8 and sales in D2:D8, the formula below adds the North total of 430 to the South total of 190 and returns 620.
=SUMIFS(D2:D8,A2:A8,"North")+SUMIFS(D2:D8,A2:A8,"South")
For several alternatives, a helper column, a PivotTable, or a carefully designed formula may be easier to maintain than a long chain of additions. The right approach depends on whether the alternatives are fixed, how often the report changes, and whether readers need to audit the calculation.
Frequently Asked Questions
Can SUMIFS use a whole column as the criteria range?
Yes. References such as A:A and D:D are valid. They are convenient for growing lists, although bounded ranges or Excel Tables can be more efficient and readable in large workbooks.
Can I use multiple criteria from the same column?
Yes. Add that column as another criteria range with a different condition. For example, two tests can set a lower and upper bound on sales. Both tests must be true for a row to be included.
Does SUMIFS use OR or AND between criteria?
Separate criteria pairs use AND: every condition must match on the same row. To total one value or another, add separate SUMIFS results or use another suitable design.
Why does SUMIFS return zero when the text seems to match?
Check for extra spaces, spelling differences, criteria that point to the wrong cell, and ranges that start on different rows. Also verify that the values in the sum range are numbers rather than text.
Can SUMIFS criteria refer to another cell?
Yes. Put the criterion in a cell and use that reference as the criterion argument. For a comparison, join the operator to the reference with &, such as ">"&F2.
Can SUMIFS total a month of dates?
Yes. Use a date at the start of the month as the inclusive lower bound and the first date of the next month as an exclusive upper bound. This includes times on the month’s final day.
Related
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
- Data Cleaning Tools in Excel: What to Use and When
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day Value: Serial Dates and the DAY FunctionAn Excel day value can mean either the serial number Excel uses to store a date or the…
- COUNTIF in Excel: Syntax, Examples, and Common FixesIn Excel, use COUNTIF to count cells in one range that meet one condition. If the values you…
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Excel Formula for High and Low Values: MIN and MAXIn Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest.…
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If…
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.