To use Excel SUMIFS with values in a list, put the values in a criteria range and wrap SUMIFS in SUM. For example, with categories in B2:B7, regions in C2:C7, amounts in D2:D7, and the categories to include in G2:G3, use =SUM(SUMIFS($D$2:$D$7,$B$2:$B$7,$G$2:$G$3,$C$2:$C$7,"East")). In the example below, it returns 240 by adding East-region amounts for every category in the list.
How SUMIFS handles conditions
SUMIFS adds numbers from a sum range only when corresponding cells meet every condition you specify. Its basic argument order is sum_range, criteria_range1, criteria1, followed by any additional criteria-range and criteria pairs. The ranges need to line up: if the sum range has six rows, each criteria range should cover those same six rows.
A single criterion such as East selects one region. A list creates an “any of these” condition for one field: match the first category, or the second, or another listed category. If you also specify a different condition, such as region equals East, that condition is combined with the list condition. In other words, the list values are alternatives, while separate SUMIFS conditions are normally joined with AND.
In Microsoft 365 for Windows, SUMIFS can return an array of subtotals when its criterion is a range such as G2:G3. SUM then adds those subtotals into one result. This is useful because you can update the list without editing the formula. Excel for Mac and Excel for the web also support these current functions, although ribbon layouts and some interaction details can differ.
Set up a list-based SUMIFS
Use a small, clearly labelled table so it is easy to check which rows and fields the formula uses. In this example, B2:B7 contains Open, Closed, Pending, Open, Pending, and Closed; C2:C7 contains East, East, East, West, East, and East; and D2:D7 contains 120, 80, 50, 200, 70, and 40. Put Open and Pending in G2:G3. The list can sit anywhere convenient, including on a separate worksheet.
With categories in B2:B7, regions in C2:C7, amounts in D2:D7, and Open and Pending in G2:G3, this formula adds the East rows whose category appears in the list:
=SUM(SUMIFS($D$2:$D$7,$B$2:$B$7,$G$2:$G$3,$C$2:$C$7,"East"))
It returns 240: the qualifying amounts are 120 for the first Open row, 50 for the first Pending row, and 70 for the second Pending row. Their sum is 120 + 50 + 70 = 240. The Open row in West is excluded, as are both Closed rows. Dollar signs make the ranges absolute, so they do not shift if you copy the formula to another cell.
For the clearest setup, keep one criterion per list cell, avoid blank cells within the list, and use the same kind of value as the data being matched. A short label above the list, such as Categories to include, can help another workbook user understand why the formula changes when the list changes.
Add more conditions to the calculation
You can add criteria pairs after the list-based condition. For example, a threshold on the amount field can be added alongside the category list and East region condition. These are all requirements for an individual row: its category must be listed, its region must be East, and its amount must be greater than 50.
With categories in B2:B7, regions in C2:C7, amounts in D2:D7, and Open and Pending in G2:G3, this formula applies that additional amount condition:
=SUM(SUMIFS($D$2:$D$7,$B$2:$B$7,$G$2:$G$3,$C$2:$C$7,"East",$D$2:$D$7,">50"))
The result is 190. The qualifying amounts are 120 and 70; the 50 amount does not satisfy the strict greater-than condition. To include 50 as well, change the criterion to “>=50”. Criteria such as greater-than or less-than operators are written as text, and a cell reference can be joined to an operator when the threshold is stored in a cell.
SUMIFS is not case-sensitive for ordinary text matching, so Open and OPEN are treated as equivalent. It also supports wildcard characters such as * and ? in text criteria. That is convenient for partial matches, but it matters if a list item literally contains one of those characters: Excel may interpret it as a wildcard rather than as a literal symbol.
Prevent duplicate list items from inflating the total
The SUM(SUMIFS(…)) approach calculates a subtotal for each item in the list and adds those subtotals. Therefore, a repeated criterion can count the same source rows more than once. This often happens when a list is assembled from pasted data or generated from several sources. Remove duplicates from the criteria list if each category should contribute only once.
If duplicates may remain, a row-based membership test can ensure each source row contributes at most once. With categories in B2:B7, regions in C2:C7, amounts in D2:D7, and a list in G2:G4 containing Open, Pending, and Open, use:
=SUMPRODUCT(($C$2:$C$7="East")*--(COUNTIF($G$2:$G$4,$B$2:$B$7)>0)*$D$2:$D$7)
This returns 240, not 360. COUNTIF checks whether each row’s category occurs at least once in the list; the greater-than-zero test turns repeated list entries into a single true-or-false match for that row. SUMPRODUCT then includes each East amount once if its category is present. This alternative is more complex, so the straightforward SUMIFS pattern is usually easier when the list is known to contain unique items.
Choose the right approach for older Excel versions
In current Microsoft 365 Excel, the range-as-criterion pattern is convenient and usually needs no special entry step. In older Excel versions, array behavior can differ: a formula that passes a multi-cell criterion range to SUMIFS may not produce the combined result in the same way. If a workbook must work in an older installation, test it in that version rather than assuming its calculation behavior matches Microsoft 365.
One compatible, simple option for a very short list is to add separate SUMIFS calculations, one per item. That is transparent but becomes cumbersome as the list grows or changes. Another option is the SUMPRODUCT and COUNTIF membership formula above. It handles repeated criteria without double-counting, but should be checked carefully if adapted to larger or more complicated datasets. Avoid entering a multi-item formula with Ctrl+Shift+Enter unless the specific older-version method you are using calls for it; array-entry requirements depend on the formula and Excel version.
For a normal Microsoft 365 workbook, start with SUM around SUMIFS and a criteria range. Choose a different method only when you need compatibility with older Excel, special duplicate handling, or a condition that SUMIFS cannot express conveniently.
Keep the criteria list easy to maintain
A criteria list works best when it is maintained as data rather than embedded in the formula. Adding or removing a value in the list then changes which records qualify without rewriting the calculation. If the list will grow, consider converting the source data and the list into Excel tables using Insert > Table. Table references can make formulas easier to read and can expand as rows are added, though the exact formula will look different from the fixed-range example.
- Use consistent spelling and remove accidental leading or trailing spaces. A value that looks like Open may contain an invisible space and fail to match.
- Keep numeric criteria numeric. A number stored as text may not match a true numeric value as expected.
- Use genuine Excel dates for date criteria. Dates are stored as serial numbers, so text that merely looks like a date can behave differently.
- Check that every criteria range covers the same rows as the sum range; mismatched dimensions can produce an error or an unintended calculation.
- When a formula is copied, verify that the data ranges remain anchored and that the criteria-list reference moves only if you intend it to.
A list can be vertical or horizontal, but a vertical list is usually easier to review and extend. If the source table is filtered, remember that SUMIFS evaluates matching cells in the ranges; it does not automatically restrict its total to only visible rows. That distinction can explain why a result includes records hidden by a filter.
Common errors and checks
If the result is zero, first test whether the list values exactly correspond to the source values. Look for spaces, different punctuation, numbers stored as text, or a region value that does not match the criterion. Then check that the sum range and each criteria range start and end on the same rows. A formula can be syntactically valid yet return zero because no row meets all of its conditions simultaneously.
If the result is too large, check for duplicate items in the criteria list and confirm that the same source rows are not being counted by repeated criteria. Also confirm that the formula’s criteria ranges do not include unrelated records. If the result is unexpectedly small, look for blanks or misspellings in the list and confirm that numeric amounts are actually numbers rather than text.
SUMIFS uses AND logic between separate criteria pairs. For example, adding Region = East and Month = January means a row must satisfy both. It does not mean East or January. To express OR across different fields, you may need separate calculations or a different formula structure. Across one field, a criteria list is the natural way to express “any of these values.”
Frequently Asked Questions
Can SUMIFS match any value in a list?
Yes. In Microsoft 365, use the list range as a SUMIFS criterion and wrap the result in SUM. The list values act as alternatives for that criteria field; additional criteria pairs still apply to every qualifying row.
Why do I need SUM around SUMIFS?
With a multi-cell criterion, SUMIFS can return one subtotal per list item. SUM adds those subtotals to produce one total. Without SUM, the formula may return an array of results rather than the single combined number you want.
Does SUMIFS with a list count duplicate values twice?
It can. The formula adds a subtotal for each occurrence in the criteria list, so a repeated item can add the same matching rows again. Remove duplicates or use a membership-based approach such as SUMPRODUCT with COUNTIF when each source row must count only once.
Can the list contain numbers or dates?
Yes. The list can contain text, numbers, or dates, provided its values correspond to the values in the source criteria range. For dates, use real Excel dates rather than text strings that only look like dates, and make sure numeric values are not inconsistently stored as text.
Does this work in Excel for Mac or Excel for the web?
Current Excel for Mac and Excel for the web support SUMIFS and SUM. The formula logic is the same; ribbon placement and interface details can differ. Older Excel versions may handle multi-cell criteria differently, so check compatibility if the workbook will be opened in an older release.
Related
- Excel Shortcut to Select an Entire Column on Windows
- Excel Shortcut to Insert an Entire Row
- Excel Unmerge Cells Shortcut: Steps, Limits, and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP col_index_num: How to Choose the Right NumberIn Excel’s VLOOKUP function, col_index_num is the number of the column to return from the selected table range,…
- Excel IFS Function for Data Analysis: Rules and ExamplesExcel’s IFS function tests conditions in order and returns the result for the first condition that is TRUE.…
- Excel Date Plus Years: Formula and Leap-Year RulesTo add years to a date in Excel, use =EDATE(A2,12*B2), where A2 contains the starting date and B2…
- Freeze Function in Excel: Freeze Panes or FormulasTo keep headings visible while you scroll, select the cell just below and to the right of the…
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
- 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…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.