The SUMIFS function in Excel is a powerful function for adding values that meet multiple criteria. It is especially useful when you need to analyze large datasets and calculate totals based on several conditions at the same time.
For example, you can use SUMIFS to calculate:
- Total sales for a specific product in a specific region
- Revenue generated by a salesperson during a particular month
- Expenses for a department within a date range
- Orders above a certain value from a particular customer
- Sales between two dates
- Values matching multiple text, number, or date conditions
In this SUMIFS Function in Excel: Ultimate Guide, you’ll learn the syntax, arguments, criteria, comparison operators, dates, wildcards, cell references, multiple conditions, practical examples, common mistakes, and the difference between SUMIFS, SUMIF, and SUMPRODUCT.
What Is the SUMIFS Function in Excel?
The SUMIFS function adds values that meet two or more criteria.
Unlike SUMIF, which is designed primarily for a single condition, SUMIFS allows you to evaluate multiple conditions simultaneously.
For example, suppose you have this sales data:
| Product | Region | Sales |
|---|---|---|
| Laptop | East | 1,200 |
| Monitor | West | 500 |
| Laptop | West | 1,500 |
| Laptop | East | 1,800 |
| Monitor | East | 700 |
To calculate the total sales for Laptop in the East region, use:
=SUMIFS(C2:C6,A2:A6,"Laptop",B2:B6,"East")Excel checks both conditions:
- Product = Laptop
- Region = East
The matching sales are:
1,200 + 1,800 = 3,000Therefore, the result is:
3,000
SUMIFS Syntax in Excel
The syntax of SUMIFS is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)The important difference between SUMIF and SUMIFS is the position of the sum_range.
SUMIFS Arguments
| Argument | Required? | Description |
|---|---|---|
sum_range | Yes | The cells containing the values to add |
criteria_range1 | Yes | The first range to evaluate |
criteria1 | Yes | The first condition |
criteria_range2 | No | The second range to evaluate |
criteria2 | No | The second condition |
| Additional criteria | No | Additional range/criteria pairs |
You can add multiple criteria pairs to a SUMIFS formula.
How Does SUMIFS Work?
SUMIFS evaluates each row against all specified conditions.
For example:
=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")Excel checks each row:
Product = Laptop?
AND
Region = East?Only rows satisfying both conditions are included in the total.
This is important:
SUMIFS uses AND logic between criteria pairs.
If you specify three conditions, a row must satisfy all three conditions before its value is included.
SUMIFS With Multiple Criteria
The main advantage of SUMIFS is its ability to apply multiple conditions.
Suppose you have:
| Product | Region | Sales |
|---|---|---|
| Laptop | East | 1,200 |
| Laptop | West | 1,500 |
| Monitor | East | 700 |
| Laptop | East | 1,800 |
| Monitor | West | 900 |
To calculate Laptop sales in the East region:
=SUMIFS(C2:C6,A2:A6,"Laptop",B2:B6,"East")The formula uses two criteria:
Product = Laptop
Region = EastResult:
3,000SUMIFS With Text Criteria
SUMIFS can evaluate text conditions such as product names, departments, customers, or regions.
For example:
=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")This calculates the total sales where:
- Product is Laptop
- Region is East
Text criteria should generally be enclosed in quotation marks.
SUMIFS Using Cell References
Instead of hard-coding criteria, you can reference cells.
Suppose:
E2containsLaptopF2containsEast
Use:
=SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)Now the formula is dynamic.
If E2 changes to Monitor, the result automatically updates.
If F2 changes to West, the formula calculates the total for the new combination.
This technique is extremely useful for:
- Dashboards
- Reports
- Interactive worksheets
- Drop-down menus
- Management summaries
SUMIFS With Three or More Criteria
You can add additional criteria pairs when necessary.
Suppose your data contains:
| Product | Region | Salesperson | Sales |
|---|---|---|---|
| Laptop | East | Ahmed | 1,200 |
| Laptop | East | Sara | 1,500 |
| Laptop | West | Ahmed | 1,800 |
| Monitor | East | Ahmed | 700 |
To calculate sales where:
- Product = Laptop
- Region = East
- Salesperson = Ahmed
use:
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East",C2:C100,"Ahmed")A row is included only if all three conditions are satisfied.
SUMIFS With Numbers
SUMIFS can evaluate numerical conditions.
Suppose you have:
| Region | Sales |
|---|---|
| East | 500 |
| West | 1,500 |
| East | 2,000 |
| West | 800 |
| East | 1,200 |
To calculate East sales greater than 1,000:
=SUMIFS(B2:B6,A2:A6,"East",B2:B6,">1000")The formula evaluates two conditions:
Region = East
Sales > 1000The matching values are:
2,000 + 1,200Result:
3,200
SUMIFS With Greater Than
Use the > operator to calculate totals where a value is greater than a specified number.
For example:
=SUMIFS(C2:C100,A2:A100,"East",C2:C100,">1000")This calculates the total of values greater than 1,000 for the East region.
SUMIFS With Less Than
Use < when you want to include only values below a specified number.
For example:
=SUMIFS(C2:C100,A2:A100,"East",C2:C100,"<1000")This calculates the total for East where sales are less than 1,000.
SUMIFS With Greater Than or Equal To
Use >= when the specified value should also be included.
For example:
=SUMIFS(C2:C100,A2:A100,"East",C2:C100,">=1000")This includes values:
- Greater than 1,000
- Equal to 1,000
SUMIFS With Less Than or Equal To
Use <= when the calculation should include values equal to the specified limit.
=SUMIFS(C2:C100,A2:A100,"East",C2:C100,"<=1000")This includes values less than or equal to 1,000.
SUMIFS With Not Equal To
The <> operator means not equal to.
For example:
=SUMIFS(C2:C100,A2:A100,"<>Laptop")This adds values where the corresponding product is not Laptop.
You can combine it with other conditions:
=SUMIFS(D2:D100,A2:A100,"<>Laptop",B2:B100,"East")This calculates East sales excluding Laptop.
SUMIFS With Equal To
You can explicitly use the = operator:
=SUMIFS(C2:C100,A2:A100,"=Laptop",B2:B100,"=East")For text criteria, the = sign is usually optional.
Therefore, this is equivalent:
=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")SUMIFS With Dates
SUMIFS is extremely useful for date-based calculations because it can evaluate multiple date conditions.
Suppose you have:
| Date | Sales |
|---|---|
| 01/01/2026 | 500 |
| 05/01/2026 | 750 |
| 10/01/2026 | 900 |
| 15/01/2026 | 1,200 |
| 20/01/2026 | 800 |
To calculate sales after January 10, 2026:
=SUMIFS(B2:B6,A2:A6,">"&DATE(2026,1,10))The & operator combines the comparison operator with the date.
SUMIFS With a Date in Another Cell
Suppose E2 contains the date:
10/01/2026You can use:
=SUMIFS(B2:B100,A2:A100,">"&E2)This calculates the total for dates after the date in E2.
You can change E2 without modifying the formula.
SUMIFS Between Two Dates
One of the most common uses of SUMIFS is calculating totals between two dates.
Suppose:
E2= Start DateF2= End Date
Use:
=SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2)This means:
Date >= Start Date
AND
Date <= End DateThis is one of the most useful SUMIFS patterns for financial reports, sales reports, and monthly analysis.
SUMIFS With a Date Range and Another Condition
You can combine a date range with other conditions.
For example, calculate Laptop sales in the East region between two dates:
=SUMIFS(D2:D100,
A2:A100,"Laptop",
B2:B100,"East",
C2:C100,">="&F2,
C2:C100,"<="&G2)Where:
- Column A = Product
- Column B = Region
- Column C = Date
- Column D = Sales
F2= Start DateG2= End Date
This formula demonstrates the real power of SUMIFS.
SUMIFS With Wildcards
SUMIFS supports the same wildcard characters commonly used with SUMIF.
The two primary wildcards are:
| Wildcard | Meaning |
|---|---|
* | Any number of characters |
? | Exactly one character |
Wildcards allow you to perform flexible text matching.
Using the Asterisk (*) Wildcard With SUMIFS
The * wildcard represents any number of characters.
For example:
=SUMIFS(B2:B100,A2:A100,"Laptop*")This can match:
- Laptop
- Laptop Pro
- Laptop Air
- Laptop Business
You can also combine the wildcard with another condition:
=SUMIFS(C2:C100,A2:A100,"Laptop*",B2:B100,"East")This calculates East sales for products beginning with Laptop.
Using the Question Mark (?) Wildcard With SUMIFS
The ? wildcard represents exactly one character.
For example:
=SUMIFS(B2:B100,A2:A100,"A??")This matches three-character values beginning with A.
The question mark is useful when the exact character is unknown but the length and general pattern are known.
SUMIFS With a Partial Text Match
You can use * before and after a keyword to find the keyword anywhere within the text.
For example:
=SUMIFS(B2:B100,A2:A100,"*Laptop*")This can match:
- Laptop
- Gaming Laptop
- Business Laptop
- Laptop Pro
- Professional Laptop
Compare the patterns
Starts with Laptop:
"Laptop*"Ends with Laptop:
"*Laptop"Contains Laptop:
"*Laptop*"These wildcard patterns are particularly useful when working with product descriptions or inconsistent text labels.
SUMIFS With Blank Cells
You can use SUMIFS to calculate totals where a criteria range contains blank cells.
For example:
=SUMIFS(B2:B100,A2:A100,"")This sums values in column B where column A is blank.
To find values where the criteria cells are not blank:
=SUMIFS(B2:B100,A2:A100,"<>")You can combine this with additional criteria as well.
SUMIFS With Dynamic Comparison Criteria
Suppose E2 contains:
1000You want to calculate sales greater than the value in E2.
Use:
=SUMIFS(B2:B100,B2:B100,">"&E2)The & operator combines:
>with:
1000to create:
>1000This allows users to change the threshold without modifying the formula.
SUMIFS Across Worksheets
SUMIFS can reference ranges on another worksheet.
Suppose your data is on a worksheet named Sales.
You can use:
=SUMIFS(Sales!C2:C100,Sales!A2:A100,"Laptop",Sales!B2:B100,"East")If the worksheet name contains spaces:
=SUMIFS('Sales Data'!C2:C100,'Sales Data'!A2:A100,"Laptop",'Sales Data'!B2:B100,"East")SUMIFS With Excel Tables
SUMIFS works particularly well with Excel Tables.
Suppose you have a table called SalesTable with these columns:
- Product
- Region
- Sales
You can write:
=SUMIFS(SalesTable[Sales],SalesTable[Product],"Laptop",SalesTable[Region],"East")This is easier to read than large cell references.
It also provides a major advantage when new records are added because Excel Tables automatically expand.
SUMIFS With Cell References
Suppose:
E2= ProductF2= Region
You can use:
=SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)This creates a dynamic two-condition calculation.
For example:
| E2 | F2 |
|---|---|
| Laptop | East |
The formula calculates:
Total Laptop sales in East.
Change E2 to Monitor and F2 to West, and the result updates automatically.
SUMIFS With Multiple Cell References
You can build a fully dynamic report using cell references for every condition.
For example:
F2= ProductG2= RegionH2= Salesperson
Formula:
=SUMIFS(D2:D100,A2:A100,F2,B2:B100,G2,C2:C100,H2)This allows the user to select or enter different criteria without editing the formula.
SUMIFS and AND Logic
SUMIFS uses AND logic between criteria.
For example:
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East",C2:C100,"Ahmed")A row must satisfy all three conditions:
Product = Laptop
AND
Region = East
AND
Salesperson = AhmedOnly matching rows are included.
SUMIFS and OR Logic
SUMIFS does not directly provide an OR condition within a single criteria pair.
For example, suppose you want:
Sales for either Laptop OR Monitor.
One simple approach is to use two SUMIFS formulas:
=SUMIFS(C2:C100,A2:A100,"Laptop")+SUMIFS(C2:C100,A2:A100,"Monitor")Another option in newer Excel versions is to use an array constant:
=SUM(SUMIFS(C2:C100,A2:A100,{"Laptop","Monitor"}))The best approach depends on your Excel version and the complexity of the calculation.
SUMIFS Is Not Case-Sensitive
SUMIFS, like SUMIF, is not case-sensitive when evaluating text criteria.
For example, these are generally treated as equivalent:
Laptop
laptop
LAPTOPIf you need case-sensitive conditional calculations, you will need an alternative approach involving functions such as EXACT and array-based calculations.
Common SUMIFS Errors
1. Incorrect Argument Order
One of the most common mistakes is confusing SUMIFS with SUMIF.
SUMIF:
=SUMIF(range,criteria,sum_range)SUMIFS:
=SUMIFS(sum_range,criteria_range,criteria,...)Notice that SUMIFS starts with the sum range.
2. Mismatched Range Sizes
All ranges used in SUMIFS should normally have compatible dimensions.
For example:
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East")is properly aligned.
Avoid formulas where one range covers significantly different rows.
3. Forgetting Quotation Marks
Text criteria should generally be enclosed in quotation marks:
=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")4. Forgetting the & Operator
If the criterion is stored in a cell, comparison operators must be combined with the cell reference.
Incorrect:
=SUMIFS(B2:B100,B2:B100,">E2")Correct:
=SUMIFS(B2:B100,B2:B100,">"&E2)5. Numbers Stored as Text
If your source data contains numbers stored as text, SUMIFS may return unexpected results.
Check imported data carefully and ensure numerical columns contain actual numbers.
6. Dates Stored as Text
SUMIFS date criteria work correctly when the cells contain real Excel dates.
If dates are stored as text, date comparisons may not work as expected.
SUMIFS vs SUMIF
The main difference is the number of criteria.
| Feature | SUMIF | SUMIFS |
|---|---|---|
| One condition | Yes | Yes |
| Multiple conditions | No | Yes |
| Main use | Simple conditional totals | Multi-condition totals |
| Syntax starts with | Criteria range | Sum range |
SUMIF
=SUMIF(A2:A100,"Laptop",B2:B100)SUMIFS
=SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East")A simple rule:
One criterion → SUMIF
Multiple criteria → SUMIFS
SUMIFS vs SUMPRODUCT
SUMPRODUCT can perform more advanced conditional calculations, especially when you need custom logical operations or array-based calculations.
For standard multi-condition sums, however, SUMIFS is usually easier to read and maintain.
For example:
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East")is much easier to understand than a more complex SUMPRODUCT construction for the same basic requirement.
Real-World SUMIFS Examples
Example 1: Sales by Product and Region
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East")Calculates Laptop sales in the East region.
Example 2: Revenue by Salesperson and Month
=SUMIFS(D2:D100,C2:C100,"Ahmed",A2:A100,">="&F2,A2:A100,"<="&G2)Calculates Ahmed’s revenue between the dates stored in F2 and G2.
Example 3: Expenses by Department and Year
=SUMIFS(C2:C100,A2:A100,"Marketing",B2:B100,2026)Calculates Marketing expenses for 2026.
Example 4: High-Value Sales in a Region
=SUMIFS(C2:C100,A2:A100,"East",C2:C100,">5000")Calculates East-region sales greater than 5,000.
Example 5: Product Sales Within a Date Range
=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,">="&F2,B2:B100,"<="&G2)Calculates Laptop sales between the start and end dates.
Example 6: Dynamic Multi-Criteria Report
If:
F2= ProductG2= RegionH2= Salesperson
use:
=SUMIFS(D2:D100,A2:A100,F2,B2:B100,G2,C2:C100,H2)This is an excellent pattern for interactive Excel dashboards.
SUMIFS for Excel Dashboards
SUMIFS is one of the most useful functions for building interactive dashboards.
For example, you could allow users to select:
Product: Laptop
Region: East
Salesperson: Ahmed
Start Date: 01/01/2026
End Date: 31/01/2026
Then use:
=SUMIFS(SalesTable[Sales],
SalesTable[Product],F2,
SalesTable[Region],G2,
SalesTable[Salesperson],H2,
SalesTable[Date],">="&I2,
SalesTable[Date],"<="&J2)The result dynamically changes whenever the user changes any selection.
SUMIFS Best Practices
1. Use Excel Tables
Structured references make SUMIFS formulas easier to read and automatically expand with new records.
2. Use cell references
Instead of hard-coding criteria, use cells or drop-down lists.
3. Keep criteria ranges aligned
Make sure all criteria ranges correspond to the same records.
4. Use SUMIFS for multiple conditions
Don’t use complicated workarounds when SUMIFS can perform the calculation directly.
5. Validate your data
Check for:
- Extra spaces
- Inconsistent text
- Numbers stored as text
- Dates stored as text
- Incorrect date formats
6. Use clear criteria cells
When building dashboards, place criteria in clearly labeled cells.
SUMIFS Formula Cheat Sheet
| Requirement | Formula |
|---|---|
| Two text criteria | =SUMIFS(C2:C100,A2:A100,"Laptop",B2:B100,"East") |
| Three criteria | =SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"East",C2:C100,"Ahmed") |
| Greater than | =SUMIFS(B2:B100,B2:B100,">1000") |
| Less than | =SUMIFS(B2:B100,B2:B100,"<1000") |
| Greater than or equal | =SUMIFS(B2:B100,B2:B100,">=1000") |
| Less than or equal | =SUMIFS(B2:B100,B2:B100,"<=1000") |
| Not equal | =SUMIFS(C2:C100,A2:A100,"<>Laptop") |
| Using cell references | =SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2) |
| After a date | =SUMIFS(B2:B100,A2:A100,">"&E2) |
| Between two dates | =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2) |
| Begins with text | =SUMIFS(B2:B100,A2:A100,"Laptop*") |
| Contains text | =SUMIFS(B2:B100,A2:A100,"*Laptop*") |
| Blank cells | =SUMIFS(B2:B100,A2:A100,"") |
| Non-blank cells | =SUMIFS(B2:B100,A2:A100,"<>") |
Frequently Asked Questions About SUMIFS
What is the SUMIFS function in Excel?
SUMIFS is an Excel function used to add values that meet multiple criteria.
What is the syntax of SUMIFS?
The syntax is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)What is the difference between SUMIF and SUMIFS?
SUMIF is designed primarily for one condition, while SUMIFS allows you to specify multiple conditions.
How many criteria can SUMIFS handle?
SUMIFS supports multiple range-and-criteria pairs, allowing you to evaluate numerous conditions within one formula.
Can SUMIFS work with dates?
Yes. SUMIFS is particularly useful for date ranges because you can combine >= and <= conditions.
Can SUMIFS use cell references?
Yes. You can use cell references for text, numbers, dates, and comparison criteria.
Can SUMIFS use wildcards?
Yes. SUMIFS supports * and ? wildcards for flexible text matching.
Is SUMIFS case-sensitive?
No. SUMIFS is not case-sensitive when comparing text.
Can SUMIFS work across worksheets?
Yes. SUMIFS can reference ranges located on other worksheets.
Can SUMIFS use OR logic?
Not directly within a standard criteria pair. You can often achieve OR logic by adding multiple SUMIFS calculations or using an array-based approach.
Conclusion
The SUMIFS function in Excel is an essential tool for analyzing data when you need to apply multiple conditions simultaneously. It can handle text, numbers, dates, comparison operators, cell references, wildcards, and combinations of several criteria.
The basic syntax to remember is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)The most important distinction is:
SUMIF is primarily for one condition, while SUMIFS is designed for multiple conditions.
Once you understand how SUMIFS handles multiple criteria, date ranges, comparison operators, wildcards, and dynamic cell references, you can use it to build powerful sales reports, financial analyses, management dashboards, and automated Excel summaries.
Inar Learn 