The direct answer: to exclude multiple criteria in Excel’s SUMIFS, you use the “not equal to” operator (<>) combined with the criteria range, and you add one criteria pair for each exclusion. For example, to sum sales in column B while excluding “Product A” in column A and “Region East” in column C, the formula is =SUMIFS(B2:B100, A2:A100, "<>Product A", C2:C100, "<>East"). This works because SUMIFS applies all criteria simultaneously (AND logic), so a row is included only if it meets every condition.
How SUMIFS Handles Exclusions
SUMIFS sums values in a range when one or more conditions are true. Each condition is a pair: a criteria range and a criteria. The criteria can be a number, text, expression, or cell reference. When you use <> (not equal to) in the criteria, you tell Excel to exclude matching values.
Key behavior: SUMIFS uses AND logic across all criteria pairs. That means a cell is added to the total only if every criteria is satisfied. If you want to exclude multiple items, you add multiple criteria pairs, each with its own <> condition. Excel will exclude rows that match any of the excluded values, because each row must pass all conditions to be included.
Example layout: Assume column A has product names (A2:A10), column B has sales amounts (B2:B10), and column C has regions (C2:C10). To sum sales for all products except “Widget” and all regions except “West”, use:
=SUMIFS(B2:B10, A2:A10, "<>Widget", C2:C10, "<>West")
If your data has 100 rows, adjust the ranges accordingly. The formula returns the total of all sales where product is not Widget and region is not West.
Using Cell References for Exclusions
Hardcoding text like “Widget” in the formula works, but it’s often better to put the excluded values in cells so you can change them without editing the formula. For example, put “Widget” in cell E1 and “West” in cell E2. Then use:
=SUMIFS(B2:B100, A2:A100, "<>"&E1, C2:C100, "<>"&E2)
The ampersand (&) concatenates the not-equal operator with the cell value. This makes the formula dynamic—change E1 or E2 and the sum updates automatically.
Be careful: if the cell is empty, the criteria becomes <> (not equal to nothing), which effectively includes all rows, so that’s fine. But if you later type a value, it will exclude that value.
Excluding Multiple Criteria from the Same Column
Sometimes you need to exclude several values from the same column, like excluding both “Widget” and “Gadget” from column A. SUMIFS doesn’t allow you to put multiple values in one criteria pair, but you have two options:
- Add multiple criteria pairs for the same range:
=SUMIFS(B2:B100, A2:A100, "<>Widget", A2:A100, "<>Gadget"). This works because each pair is a separate condition, and a row must not be Widget AND must not be Gadget. - Use SUMPRODUCT with wildcards (for partial matches): If you need to exclude anything containing a certain text, use
=SUMPRODUCT((B2:B100)*(ISNUMBER(SEARCH("Widget",A2:A100))=FALSE)*(ISNUMBER(SEARCH("Gadget",A2:A100))=FALSE)). But this is more complex and only needed for partial matches.
For exact matches, the first method is simpler and faster. Note that SUMIFS is case-insensitive, so “widget” and “Widget” are treated the same.
Common Problems and Solutions
Here are frequent issues users encounter when excluding criteria in SUMIFS:
- Excluding blanks: To exclude empty cells in a range, use
"<>"(just the operator with nothing after). For example,=SUMIFS(B2:B100, A2:A100, "<>")sums only rows where column A is not blank. - Excluding zero: To exclude zero values, use
"<>0". But note that if you have text in the criteria range, this will also exclude text that equals “0” as text. - Wildcards in exclusions: If you want to exclude all products that start with “W”, use
"<>W*". The asterisk is a wildcard that matches any sequence of characters. This works in SUMIFS. - #VALUE! error: This often happens if your criteria range and sum range have different sizes. Make sure all ranges are the same number of rows and columns.
- Criteria with dates: To exclude a specific date, write it as
"<>"&DATE(2025,1,1)or use a cell reference. Be careful with date formatting; Excel stores dates as numbers.
SUMIFS vs. SUMPRODUCT for Exclusions
While SUMIFS is the go-to function, SUMPRODUCT offers more flexibility, especially when you need to exclude based on multiple criteria in the same column or use complex logic. Here’s a comparison:
- SUMIFS: Easier to read, faster on large datasets, and designed for this purpose. But it only supports AND logic and each criteria pair is a simple comparison.
- SUMPRODUCT: Can handle OR logic, array operations, and more complex conditions. For example, to sum sales where product is not Widget OR not Gadget, you’d need SUMPRODUCT because SUMIFS would require both conditions to be true, which is impossible for the same cell.
For most exclusion needs, SUMIFS is sufficient. But if you find yourself fighting the AND logic, consider SUMPRODUCT. Example: To sum sales where product is either “Widget” or “Gadget” (inclusive), you’d use =SUMPRODUCT((A2:A100="Widget")+(A2:A100="Gadget"), B2:B100). That’s not an exclusion, but it shows the flexibility.
Performance and Large Data Sets
SUMIFS is generally fast, but if you have tens of thousands of rows and many criteria, it can slow down. Here are tips:
- Use full column references (e.g., A:A) sparingly; they calculate the entire column, which is slower. Use defined ranges like A2:A10000.
- If you have many exclusions, consider using a helper column with a formula that flags rows to include, then use a single SUMIF on that helper column.
- For very large data, Excel’s Power Query (Get & Transform) can filter rows before summing, which is more efficient.
Example of helper column: In D2, enter =IF(AND(A2<>"Widget",C2<>"West"),B2,0) and drag down. Then sum column D. This is easier to maintain if you have many conditions.
Frequently Asked Questions
Can I use SUMIFS to exclude multiple criteria from different columns? Yes, just add each criteria pair as shown. Each pair is an AND condition, so all exclusions must be true for the row to be summed.
How do I exclude values that are not in a list? You can use multiple <> criteria, but if the list is long, consider using SUMPRODUCT with MATCH or a helper column. For example, to exclude a list of products in E1:E5, use =SUMPRODUCT((COUNTIF(E1:E5,A2:A100)=0)*B2:B100).
Why does my SUMIFS return 0 when I exclude a text? Check for leading/trailing spaces in your data. Use TRIM to clean data. Also check that the text in the criteria matches exactly (case doesn’t matter, but spaces do).
Can I exclude blank cells and specific text at the same time? Yes, use "<>" for blanks and "<>Text" for text, as separate criteria pairs. For example, =SUMIFS(B2:B100, A2:A100, "<>", A2:A100, "<>Pending").
Does SUMIFS work with dates to exclude weekends? SUMIFS alone can’t exclude weekends directly, but you can use a helper column with WEEKDAY function, then sum with a criteria like "<>1" and "<>7" (if 1=Sunday, 7=Saturday).
Is there a way to exclude multiple criteria using wildcards? Yes, you can use wildcards with <>. For example, "<>*Temp*" excludes any cell containing “Temp”. But note that wildcards work only with text, not numbers.
Related
- Excel Power Query Issues: Fixes & Common Errors
- Excel Power Query Load from Folder: Full Guide
- Excel Power Query Merge 3 Queries: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH…
- Filter Excel by Highlighted CellsYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the column containing the highlighted…
- Excel MATCH Expressions: Syntax and ExamplesExcel’s MATCH function finds a value in a one-dimensional range and returns its position within that range—not the…
- Excel Sum Rows Based on Cell Value: Formulas That WorkTo total values in rows where a cell matches a condition, use Excel’s SUMIF function. With categories in…
- Large Excel Cheat Sheet: Formulas, Shortcuts & TipsA large Excel cheat sheet is a quick reference for common formulas, keyboard shortcuts, data tools, formatting, and…
- Excel Formula for Percent Change: Examples and TipsTo calculate percent change in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.