Free Excel workbook
Download the SUMIFS Practice File
Download the exact workbook from the tutorial.
No spam. Unsubscribe anytime.
If you work with data in Excel, you might need to sum values based on multiple conditions.
In this tutorial, we’ll show you step-by-step how to sum the profits for the Utility Division (cell G2) from both Asia and Europe (cells G3 & H3) when your data has only one column for regions.
How Do You Write a SUMIFS Formula with Multiple Criteria in One Column?
Step 1: Download the Practice File
Click here to download the practice file to follow along.
Step 2: Write the SUMIFS formula
If you have seen previous posts on the SUMIFS function, you know that creating a formula that will sum the profits if the Division is Utility and the Region is Asia would be easy.
- Profit (column D)
- Division (column A)
- Utility (cell G2)
- Region (column B)
- Asia (cell G3)
=SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3)
Suppose we extend the argument for “which Region” from a single cell (G3) to multiple cells (G3:H3)? This way, we can include both Asia and Europe.
=SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3)
We are presented with two results: one for “Utility – Asia” and one for “Utility – Europe”.
Why Does This Work?
Dynamic arrays allow a single formula to handle multiple inputs and return multiple results.
Excel automatically spills the results into adjacent cells, saving you from writing multiple formulas.
Important Note for Legacy Excel Users
If you’re using an older version of Excel (before Microsoft 365 or Office 2021), this formula won’t work. You’ll get an error because older versions can’t handle multiple results in a single formula.
Featured Course
Master Excel’s Essential Modern Functions
How Do You Combine Multiple SUMIFS Results into One Total?
Whether you are running Microsoft 365 Excel or legacy Excel, our objective of a single, combined result is achieved the same way.
Our solution is to wrap (i.e., nest) the SUMIFS function within a SUM function.
=SUM(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )
How Does This Formula Work in Older Excel Versions?
If you’re using an older version of Excel (before Microsoft 365 or Office 2021), the dynamic arrays feature isn’t available.
When you try to use the formula with multiple criteria, you’ll get an error.
Here’s how to make it work:
- Use Ctrl+Shift+Enter:
- Instead of pressing the Enter key, press Ctrl+Shift+Enter to finalize the formula.
- Excel will wrap the formula in curly braces
{ }and treat it as an array formula.
When Should You Use SUMPRODUCT Instead of SUM with SUMIFS?
If you want a simpler and more compatible solution, you can use the SUMPRODUCT function.
This method works in all versions of Excel, including older ones.
=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )
Why Use SUMPRODUCT?
- It doesn’t require the
Ctrl+Shift+Enterkey combination. - It’s easier to read and works seamlessly in both legacy and modern Excel.
- If you’re sharing the file with non-Microsoft 365 users, this approach avoids errors and confusion.
Featured Course
Excel Essentials for the Real World
How Do You Add More Criteria Without Rewriting the Formula?
You may need to add more criteria to your formula as your dataset grows.
For example, if you want to include Australia alongside Asia and Europe, you’ll need to adjust your formula to account for this new region.
Why Update the Formula?
The original formula sums profits for specific regions listed in cells G3:H3 (Asia and Europe).
If you add Australia to the list in cell I3, your formula won’t automatically include it unless you update the range.
Here’s the original formula:
=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )Update the Formula for Australia
If you add Australia in cell I3, adjust the range in the formula to include this new region:
Updated Formula:
=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:I3) )This change extends the range from G3:H3 to G3:I3, including Asia, Europe, and Australia.
Can You Use SUM + FILTER Instead of SUMIFS for OR Logic?
Yes. If you’re running Microsoft 365 or Excel 2021+, the FILTER function gives you a cleaner way to solve this same problem. It handles OR logic natively, which SUMIFS does not.
Here’s the core difference: SUMIFS uses AND logic by default. A cell must meet all criteria to be included. When you need OR logic (Asia or Europe), you have to work around it with arrays and SUM wrappers. FILTER was built for exactly this kind of flexibility.
The Formula
Using the same dataset from this tutorial, here’s how to sum profits for the Utility division where the region is Asia or Europe:
=SUM(FILTER(D2:D28, (A2:A28=G2) * ((B2:B28=G3)+(B2:B28=H3))))How it works:
The * operator acts as AND (Division must equal Utility). The + operator acts as OR (Region can be Asia or Europe). FILTER returns all matching profit values, and SUM adds them into a single total.
Behind the scenes, Excel treats TRUE as 1 and FALSE as 0. Multiplying two conditions means both must be true (1 × 1 = 1). Adding two conditions means either can be true (0 + 1 = 1). Any non-zero result passes through the filter.
Why Use FILTER + SUM Over SUMIFS?
There are three practical reasons to consider this approach.
Readability. The formula reads more like plain logic: “Sum the profits where Division equals Utility AND Region equals Asia or Europe.” No array workarounds or SUMPRODUCT wrappers needed.
Flexibility. You can swap SUM for any aggregation function. Need the average instead? Use =AVERAGE(FILTER(...)). Need the minimum? Use =MIN(FILTER(...)). SUMIFS only sums. FILTER works with AVERAGE, MIN, MAX, COUNT, MEDIAN, and any other function.
Scalability. Adding another OR condition is simple. To include Australia, just extend the OR portion:
=SUM(FILTER(D2:D28, (A2:A28=G2) * ((B2:B28=G3)+(B2:B28=H3)+(B2:B28=I3))))No need to adjust a criteria range reference. You add conditions directly in the formula.
When to Stick with SUMIFS
FILTER is not always the better choice.
Large datasets (100,000+ rows): SUMIFS is generally faster because it’s optimized for conditional summing. FILTER builds a temporary array of all matching values first, then aggregates. On very large ranges, that extra step can slow things down.
Backward compatibility: FILTER requires Microsoft 365 or Excel 2021+. If your files are shared with people on Excel 2019 or earlier, they’ll see a #NAME? error. In that case, the SUM(SUMIFS()) or SUMPRODUCT(SUMIFS()) approach from earlier in this guide is still your best option.
Simple single-condition sums: If you’re summing on one criterion with AND logic, SUMIFS is more direct. FILTER shines when OR logic, mixed AND/OR conditions, or non-SUM aggregations are involved.
Quick Comparison
| SUM + SUMIFS | SUM + FILTER | |
|---|---|---|
| OR logic | Requires array workaround | Built-in with + operator |
| Aggregation | SUM only | Any function (AVERAGE, MIN, MAX, etc.) |
| Excel version | All versions (with CSE in legacy) | Microsoft 365 / Excel 2021+ only |
| Large dataset speed | Faster | Slower on 100K+ rows |
| Readability | Moderate | High |
Download the Free Practice File
Ready to learn how to use Excel SUMIFS with multiple criteria in a single column?
📥 Download the Workbook Now and follow along with this step-by-step guide. It’s the easiest way to master SUMIFS and apply it to real-world tasks!
Frequently Asked Questions About SUMIFS with Multiple Criteria
Why does SUMIFS return 0 when I use multiple criteria in the same column?
SUMIFS uses AND logic by default. If you write =SUMIFS(D2:D28, B2:B28, "Asia", B2:B28, "Europe"), Excel looks for cells that are both “Asia” AND “Europe” at the same time. No single cell can be two values at once, so the result is always 0.
To apply OR logic (Asia or Europe) within the same column, pass an array of criteria and wrap the result in SUM:
=SUM(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3))
This forces SUMIFS to evaluate each criterion separately and SUM combines the results.
What is the difference between SUMIF and SUMIFS?
SUMIF accepts one condition. SUMIFS accepts multiple conditions and uses AND logic across all of them. Their syntax is also different: in SUMIF, the sum range is the last argument. In SUMIFS, the sum range comes first.
For OR logic with a single criteria column, both functions work. You can use =SUMIF(B2:B28, "Asia", D2:D28) + SUMIF(B2:B28, "Europe", D2:D28) or the array approach with SUMIFS shown in this tutorial. The SUMIFS array method scales better when you have more than two or three criteria values.
Does SUMIFS work with OR logic?
Not directly. SUMIFS applies AND logic to all its criteria pairs. To get OR behavior, you need one of these workarounds:
Pass an array constant as the criteria: =SUM(SUMIFS(D2:D28, B2:B28, {"Asia","Europe"}))
Use a cell range as the criteria: =SUM(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3))
Or use the FILTER function (Microsoft 365 / Excel 2021+): =SUM(FILTER(D2:D28, (B2:B28="Asia")+(B2:B28="Europe")))
All three return the same result. The FILTER approach is the most readable for complex conditions.
Do I still need Ctrl+Shift+Enter for SUMIFS with multiple criteria?
No, if you’re running Microsoft 365 or Excel 2021+. Dynamic arrays handle the multi-result output automatically. Just press Enter.
If you’re on Excel 2019 or earlier, you do need Ctrl+Shift+Enter to confirm the formula as a legacy array formula. Excel will add curly braces { } around it. Alternatively, wrap the formula in SUMPRODUCT instead of SUM. SUMPRODUCT processes arrays natively in all Excel versions and doesn’t require the key combination.
Can SUMIFS handle wildcards with multiple criteria in one column?
Yes, but only for text values. SUMIFS supports the asterisk (*) to match any number of characters and the question mark (?) to match a single character.
For example, to sum profits where the region starts with “A” (matching Asia, Africa, Australia): =SUMIFS(D2:D28, B2:B28, "A*")
To combine wildcards with OR logic, use the array approach: =SUM(SUMIFS(D2:D28, B2:B28, {"A*","E*"})) This sums profits for all regions starting with A or E.
Wildcards do not work with numeric criteria. If your criteria column contains numbers, use comparison operators like ">"&100 instead.
What is the maximum number of criteria SUMIFS can handle?
SUMIFS supports up to 127 criteria pairs (criteria_range + criteria). Each pair applies to a different column using AND logic. For OR logic within a single column, the practical limit depends on how you structure the array. A cell range like G3:Z3 can hold as many criteria values as you need, and =SUM(SUMIFS(sum_range, criteria_range, G3:Z3)) will evaluate all of them.
If your criteria list is long and changes frequently, consider using a named range for the criteria cells so you don’t need to update the formula range every time.
Is SUM + FILTER faster than SUM + SUMIFS?
For most workbooks, you won’t notice a difference. On very large datasets (100,000+ rows), SUMIFS is generally faster because it’s specifically optimized for conditional summing. FILTER builds a temporary array of all matching values before aggregating, which adds overhead at scale.
For typical business datasets (a few thousand to tens of thousands of rows), pick whichever formula is easier for you and your team to read and maintain. Readability matters more than a few milliseconds of calculation time.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
