How to Use SUMIFS Across Multiple Columns in Excel?
If you’re an Excel user looking to sum data based on multiple criteria across several columns, the SUMIFS function is the perfect tool for the job. It allows you to quickly and easily sum values that meet specific conditions in multiple columns, saving you time and effort compared to manually filtering your data or using complex nested formulas.
In this article, we’ll explain what the SUMIFS function does, show you how to use it with multiple columns, and provide several examples to illustrate its power and flexibility. By the end, you’ll be a SUMIFS pro, able to easily sum data in Excel based on even the most complex criteria spanning multiple columns.
What is the SUMIFS Function in Excel?
The SUMIFS function in Excel is a built-in formula that allows you to sum the values in a range that meet multiple criteria. It’s similar to the SUMIF function, but more powerful and flexible since it can handle multiple condition ranges and criteria instead of just one.
The syntax for the SUMIFS function is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Here’s what each part of the function means:
- sum_range: The range of cells that contains the values you want to sum if all the specified criteria are met.
- criteria_range1: The first range of cells to check against the first criteria. This range should be the same size as the sum_range.
- criteria1: The criteria that must be met in criteria_range1 for a value to be included in the sum. This can be a specific value, a comparison (like “>1000”), or a cell reference.
- criteria_range2, criteria2, …: Additional ranges and their associated criteria that must also be met for a value to be summed. These are optional, but you can include as many additional criteria ranges and criteria as you need.
In plain English, the SUMIFS function looks at each of the criteria ranges you provide and sums the corresponding values from the sum range only if all of the specified criteria are met. This allows you to sum data in Excel based on multiple conditions, even if those conditions span multiple columns.
How to Use SUMIFS Across Multiple Columns
To use the SUMIFS function across multiple columns in Excel, you simply need to provide additional criteria ranges and criteria for each column you want to filter on.
For example, let’s say you have a spreadsheet with sales data that includes columns for Date, Region, Sales Rep, Product, and Revenue. You want to sum the total revenue for a specific date, region, and product. You could use a SUMIFS formula like this:
=SUMIFS(revenue_range, date_range, date_criteria, region_range, region_criteria, product_range, product_criteria)
This formula would sum the values from the revenue range only for rows where the date matches date_criteria, the region matches region_criteria, and the product matches product_criteria. By adding more criteria ranges and criteria, you can make your SUMIFS formulas even more specific and targeted.
SUMIFS Multiple Columns Example
Let’s walk through a concrete example to see the SUMIFS function in action across multiple columns. Consider the following sales data table in Excel:
Date | Region | Sales Rep | Product | Revenue |
---|---|---|---|---|
1/1/2023 | North | John | Widget | $1,000 |
1/1/2023 | South | Sarah | Gadget | $1,500 |
1/2/2023 | East | Mike | Widget | $1,200 |
1/2/2023 | West | Jessica | Gizmo | $800 |
1/3/2023 | North | John | Gadget | $900 |
1/3/2023 | South | Sarah | Widget | $1,800 |
To sum the total revenue for Widgets sold in the North region on 1/1/2023, you would use this SUMIFS formula:
=SUMIFS(E2:E7, A2:A7, “1/1/2023”, B2:B7, “North”, D2:D7, “Widget”)
This formula checks the Date column (A2:A7) for “1/1/2023”, the Region column (B2:B7) for “North”, and the Product column (D2:D7) for “Widget”. For each row where all three of those conditions are true, it sums the corresponding value from the Revenue column (E2:E7). In this case, the result would be $1,000, since only the first row meets all of the specified criteria.
But the real power of SUMIFS is its flexibility. You could easily modify this formula to sum the revenue for all Widgets sold in January 2023 by changing the date criteria:
=SUMIFS(E2:E7, A2:A7, “>=1/1/2023”, A2:A7, “<=1/31/2023”, D2:D7, “Widget”)
This updated formula sums revenue values where the date is between 1/1/2023 and 1/31/2023 inclusive, and the product is Widget. The ability to use comparison operators like “>=” and “<=”, as well as wildcards and other functions, makes SUMIFS incredibly versatile.
Tips for Using SUMIFS With Multiple Columns
Here are a few tips and best practices to keep in mind when using the SUMIFS function across multiple columns in Excel:
- Use absolute cell references ($A$1) for your criteria if you plan to copy the formula to other cells. This will keep the criteria the same while allowing the sum and criteria ranges to shift relative to the formula’s location.
- Double-check that your ranges are all the same size. If one range is larger or smaller than the others, you’ll get a #VALUE! error or unexpected results.
- Leverage wildcards, comparison operators, and other functions in your criteria for even more power and flexibility. For example, “>1000” sums values over 1000, “<>North” excludes the North region, “*gadget” matches “Widget” and “Gadget”, and “~” acts as a single-character wildcard.
- Break complex SUMIFS formulas into smaller pieces to make them easier to understand and troubleshoot. You can use multiple SUMIFS functions and add or subtract them from each other as needed.
- Use SUMIFS in combination with other functions like AVERAGE, MAX, MIN, etc. to perform even more advanced calculations on your data.
Wildcards and Comparison Operators in SUMIFS
Excel’s SUMIFS function supports the following wildcards and comparison operators in criteria:
- ?: Acts as a wildcard for one character (e.g. “~4” matches “14” and “24”)
- ****: Acts as a wildcard for zero or more characters (e.g. “East” matches “East”, “Eastern”, and “Northeast”)
- >: Greater than
- >=: Greater than or equal to
- <: Less than
- <=: Less than or equal to
- <>: Not equal to
Using these in your SUMIFS criteria allows you to match partial text, compare numbers, and exclude certain values, making your formulas more targeted and flexible. For example, this formula sums revenue over $1000 for sales reps whose names start with “J”:
=SUMIFS(revenue_range, sales_rep_range, “J*”, revenue_range, “>1000”)
This kind of advanced criteria would be much harder to achieve with filters or nested IF statements.
Combining SUMIFS Formulas
If you need to sum data based on multiple sets of mutually exclusive conditions, you can use multiple SUMIFS formulas and combine them with addition, subtraction, or other math operators.
For instance, to sum revenue for the North and South regions separately, you could use two SUMIFS formulas and add them:
=SUMIFS(revenue_range, region_range, “North”) + SUMIFS(revenue_range, region_range, “South”)
This calculates the total revenue for the North and South regions individually and then adds those subtotals together. While you could achieve the same result with a single SUMIFS formula using an “OR” condition for the region, splitting it into two formulas is often easier to understand and troubleshoot.
Summary
The SUMIFS function in Excel is an incredibly powerful and flexible tool for summing data based on multiple criteria across several columns. By providing the right sum range, criteria ranges, and criteria, you can quickly calculate totals that match even the most specific and complex conditions.
The key things to remember when using SUMIFS with multiple columns are:
- SUMIFS can handle as many criteria ranges and criteria as you need
- Criteria can include wildcards, comparison operators, and other Excel functions for maximum flexibility
- Make sure all of your ranges are the same size to avoid errors
- Use absolute cell references ($A$1) for criteria you want to keep constant when copying formulas
- Break complex formulas into smaller, more manageable pieces to simplify troubleshooting and interpretation
With practice and experimentation, you’ll be amazed at all you can do with SUMIFS in Excel across multiple columns. It can help you analyze, summarize, and report on your data more efficiently and accurately than ever before.
FAQs
How do I use the SUMIFS function across multiple columns?
Can I use wildcards and comparison operators in SUMIFS criteria?
What happens if my SUMIFS ranges are different sizes?
Can I combine multiple SUMIFS functions in a single formula?
What are some tips for using SUMIFS with multiple columns effectively?

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.