Use SUMIFS to sum values that meet multiple criteria: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, =SUMIFS(B2:B20, A2:A20, "North", C2:C20, ">100") sums sales in B2:B20 where column A says “North” and column C is greater than 100. This article explains the syntax, gives real examples, and covers common pitfalls.
What SUMIFS Does and When to Use It
SUMIFS adds numbers in one column (the sum range) only when corresponding cells in one or more other columns meet specified conditions. It is the multi-criteria version of SUMIF, which handles only one condition. You would use SUMIFS when you need to total sales by region and product, or count orders by date range and status. The function works in Excel 2010 and later, including Microsoft 365, Excel 2021, Excel 2019, and Excel for the web. On Mac, the syntax and behavior are identical.
SUMIFS Syntax and Argument Order
The function has a specific order: sum range first, then pairs of criteria ranges and criteria. The syntax is:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Each criteria range must be the same size and shape as the sum range. If they are not, Excel returns #VALUE!. Criteria can be numbers, text, expressions (like “>10”), or cell references. Text criteria are not case-sensitive; “north” matches “North”.
Worked Example: Sales by Region and Product
Assume data in A1:D20: A=Region, B=Product, C=Units Sold, D=Revenue. To sum revenue for “North” region and product “Widget”:
- Layout: revenue in D2:D20, regions in A2:A20, products in B2:B20.
- Formula:
=SUMIFS(D2:D20, A2:A20, "North", B2:B20, "Widget") - If the data has 5 rows matching, the result is the sum of those 5 revenues.
You can also use cell references for criteria: put “North” in F1 and “Widget” in G1, then use =SUMIFS(D2:D20, A2:A20, F1, B2:B20, G1).
Using Wildcards and Comparison Operators
SUMIFS supports wildcards for partial text matches: * (any sequence) and ? (single character). For example, to sum sales for all products starting with “Wid”, use =SUMIFS(D2:D20, B2:B20, "Wid*"). For numeric criteria, use operators in quotes: “>100”, “<50", ">=10″. To use a cell reference with an operator, concatenate: =SUMIFS(D2:D20, C2:C20, ">"&E1) where E1 contains the threshold number.
Common Mistakes and How to Fix Them
- Wrong argument order: SUMIFS puts sum range first, unlike SUMIF which puts criteria range first. Mixing them up returns 0 or #VALUE!.
- Mismatched range sizes: All ranges must have the same number of rows and columns. If you use D2:D20 and A2:A21, you get an error.
- Using AND logic vs OR logic: SUMIFS applies AND logic—all criteria must be true. If you need OR logic (e.g., sum if region is North OR South), you need to add two SUMIFS together:
=SUMIFS(D2:D20, A2:A20, "North") + SUMIFS(D2:D20, A2:A20, "South"). - Text with spaces: Criteria text must match exactly, including leading/trailing spaces. Use TRIM on the data or criteria if needed.
- Dates as criteria: Use a date serial number or a cell reference with a real date. For example,
=SUMIFS(D2:D20, A2:A20, ">="&DATE(2024,1,1))works, but writing “1/1/2024” as text may not.
SUMIFS vs SUMPRODUCT vs FILTER
SUMIFS is the fastest and simplest for most multi-criteria sums. However, SUMPRODUCT can handle OR logic and array operations without extra helper columns. For example, to sum revenue for North or South: =SUMPRODUCT(D2:D20 * ((A2:A20="North") + (A2:A20="South"))). FILTER (available in Excel 2021 and Microsoft 365) can extract rows first, then sum: =SUM(FILTER(D2:D20, (A2:A20="North")*(B2:B20="Widget"))). FILTER is more flexible for returning the filtered data itself, but SUMIFS is more efficient for simple sums and works in older versions.
Using SUMIFS with Dates and Blank Cells
For date ranges, use comparison operators with dates. To sum revenue between Jan 1, 2024 and Jan 31, 2024, with dates in A2:A20 and revenue in B2:B20: =SUMIFS(B2:B20, A2:A20, ">="&DATE(2024,1,1), A2:A20, "<="&DATE(2024,1,31)). To sum where a cell is blank, use "" as criteria: =SUMIFS(B2:B20, C2:C20, ""). To sum where a cell is not blank, use "<>": =SUMIFS(B2:B20, C2:C20, "<>"). Note that "<>" also counts cells with text, but not truly empty cells—use ISBLANK in SUMPRODUCT if you need strict blank detection.
Frequently Asked Questions
Can SUMIFS sum across multiple columns?
No, SUMIFS accepts only one sum range. To sum multiple columns, use multiple SUMIFS added together, like =SUMIFS(B2:B20, A2:A20, "X") + SUMIFS(C2:C20, A2:A20, "X"), or use SUMPRODUCT with a range that spans columns.
How do I use OR logic in SUMIFS?
SUMIFS cannot do OR directly. Add separate SUMIFS results for each condition, or use SUMPRODUCT with addition inside the array. For example, =SUMIFS(D2:D20, A2:A20, "Red") + SUMIFS(D2:D20, A2:A20, "Blue").
Why does my SUMIFS return 0?
Common reasons: criteria text doesn't match exactly (case is ignored, but spaces and punctuation matter), criteria range and sum range are different sizes, or you used the wrong argument order. Check that your criteria are not numbers stored as text—use the VALUE function or format cells as numbers.
Can I use a cell reference for the criteria range itself?
Yes, you can refer to a range like A2:A20, but you cannot use a formula to dynamically change the range unless you use INDIRECT (which is volatile and not recommended) or a table structured reference. For dynamic ranges, consider using an Excel Table and structured references like Table1[Revenue].
Does SUMIFS work with wildcards for numeric criteria?
No, wildcards only work with text criteria. For numeric conditions, use comparison operators. If you need to match numbers that start with a digit, convert them to text first, but that is rarely needed.
Is there a limit to the number of criteria in SUMIFS?
In modern Excel, you can add up to 127 pairs of criteria ranges and criteria. That is far more than practical for most users. Performance may slow with very large datasets, but for normal use it is fine.
Related
- Excel Power Query Split Column by Delimiter
- Excel Power Query Split Data: How-To Guide
- Merge Two Tables in Excel Power Query: Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Lowercase in Excel: Shortcut, Formula, and Fast FixesExcel has no built-in keyboard shortcut that directly changes selected text to lowercase. The dependable option is the…
- Excel Filter Based on 2 Criteria: AND or ORTo filter Excel data using two criteria, apply a filter to two columns: Excel combines those conditions with…
- Excel Formula for Overdue Days: Dates and ExamplesTo calculate overdue days in Excel, put the due date in A2 and use =IF(A2="","",MAX(0,TODAY()-A2)) in another cell.…
- Combine Day, Month, and Year in Excel with a Date FormulaTo combine a day, month, and year from separate cells into a real Excel date, use the DATE…
- How to Add Hours in Excel: Formulas and Time FormatsTo add hours in Excel, enter each duration in a cell, total the cells with SUM, then format…
- Excel Filter Shortcut: How to Turn It On and Use ItTo put a filter in Excel on Windows, click a cell in your data and press Ctrl+Shift+L. Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.