Introduction
The SUMIFS function in Excel allows you to add numbers based on multiple conditions. It is useful when preparing detailed business reports, such as region-wise sales for a particular product, monthly revenue for a selected region, and department-wise expenses. In this tutorial, you will learn how to use SUMIFS with practical examples.
What is the SUMIFS Function in Excel?
SUMIFS is a conditional aggregation function that adds values only when all specified conditions are satisfied.
For example:
- Calculate Laptop sales in the North region.
- Calculate January revenue for the South region.
- Calculate HR expenses for a specific year.
- Calculate sales for a particular employee and product.
SUMIFS Function Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments:
sum_range: The cells containing the numbers you want to add.criteria_range1: The first range Excel checks.criteria1: The first condition.criteria_range2: The second range Excel checks.criteria2: The second condition.
You can add multiple pairs of criteria ranges and conditions.
Important: SUMIFS checks all specified conditions. A row is included only when every condition is satisfied.
Practical Examples of SUMIFS Function in Excel
Question 1: Calculate Region-Wise Sales for a Specific Product
Problem Statement
A company wants to calculate total Laptop sales generated in the North region.
Sales Data
| Order ID | Region | Product | Sales |
|---|---|---|---|
| O101 | North | Laptop | 50000 |
| O102 | South | Mouse | 12000 |
| O103 | North | Laptop | 45000 |
| O104 | North | Keyboard | 18000 |
| O105 | South | Laptop | 60000 |
| O106 | North | Mouse | 10000 |
| O107 | North | Laptop | 40000 |
Excel Formula
=SUMIFS(D2:D8,B2:B8,"North",C2:C8,"Laptop")
Expected Result
| Region | Product | Total Sales |
|---|---|---|
| North | Laptop | 135000 |
Explanation
D2:D8contains the sales amounts.B2:B8checks the region."North"is the first condition.C2:C8checks the product."Laptop"is the second condition.
Excel adds sales only when the region is North and the product is Laptop.
Concept Covered: Applying two conditions to calculate sales.
Question 2: Calculate Month-Wise Revenue for a Specific Region
Problem Statement
A company wants to calculate January revenue generated in the South region.
Revenue Data
| Invoice ID | Month | Region | Revenue |
|---|---|---|---|
| INV101 | January | South | 45000 |
| INV102 | January | North | 38000 |
| INV103 | February | South | 52000 |
| INV104 | January | South | 30000 |
| INV105 | February | North | 41000 |
| INV106 | January | South | 25000 |
| INV107 | March | South | 60000 |
Excel Formula
=SUMIFS(D2:D8,B2:B8,"January",C2:C8,"South")
Expected Result
| Month | Region | Total Revenue |
|---|---|---|
| January | South | 100000 |
Explanation
Excel checks two conditions:
- Month must be January.
- Region must be South.
Only the revenue values satisfying both conditions are added.
Concept Covered: Month-wise and region-wise revenue calculation.
Question 3: Calculate Department-Wise Expenses for a Specific Month
Problem Statement
An organization wants to calculate HR department expenses recorded in January.
Expense Data
| Expense ID | Department | Month | Expense |
|---|---|---|---|
| E101 | HR | January | 12000 |
| E102 | IT | January | 25000 |
| E103 | HR | February | 15000 |
| E104 | HR | January | 18000 |
| E105 | Sales | January | 20000 |
| E106 | HR | March | 10000 |
| E107 | HR | January | 14000 |
Excel Formula
=SUMIFS(D2:D8,B2:B8,"HR",C2:C8,"January")
Expected Result
| Department | Month | Total Expenses |
|---|---|---|
| HR | January | 44000 |
Explanation
Excel checks the Department column for HR and the Month column for January. It adds only the expense values that satisfy both conditions.
Concept Covered: Department-wise monthly expense reporting.
Question 4: Calculate Sales for a Specific Employee and Region
Problem Statement
A sales manager wants to calculate the total sales generated by Amit in the North region.
Employee Sales Data
| Employee | Region | Product | Sales |
|---|---|---|---|
| Amit | North | Laptop | 50000 |
| Neha | South | Mouse | 12000 |
| Amit | East | Laptop | 30000 |
| Rahul | North | Keyboard | 18000 |
| Amit | North | Mouse | 20000 |
| Neha | North | Laptop | 40000 |
| Amit | North | Laptop | 35000 |
Excel Formula
=SUMIFS(D2:D8,A2:A8,"Amit",B2:B8,"North")
Expected Result
| Employee | Region | Total Sales |
|---|---|---|
| Amit | North | 105000 |
Explanation
Excel checks the Employee column for Amit and the Region column for North. It adds the sales values only when both conditions match.
Concept Covered: Employee-wise sales with multiple conditions.
Question 5: Calculate Sales Greater Than a Specific Amount for a Selected Region
Problem Statement
A company wants to calculate North region sales where individual transactions are greater than ₹20,000.
Sales Data
| Order ID | Region | Sales |
|---|---|---|
| O101 | North | 15000 |
| O102 | South | 25000 |
| O103 | North | 35000 |
| O104 | North | 18000 |
| O105 | North | 40000 |
| O106 | South | 30000 |
| O107 | North | 25000 |
Excel Formula
=SUMIFS(C2:C8,B2:B8,"North",C2:C8,">20000")
Expected Result
| Region | Sales Condition | Total Sales |
|---|---|---|
| North | Greater than 20000 | 100000 |
Explanation
Excel checks two conditions:
- Region must be North.
- Sales must be greater than ₹20,000.
It adds only the North region transactions that satisfy the sales condition.
Concept Covered: Combining text and numerical conditions.
Question 6: Calculate Product Sales Within a Date Range
Problem Statement
A company wants to calculate Laptop sales made between 1 January 2026 and 31 January 2026.
Sales Data
| Order ID | Order Date | Product | Sales |
|---|---|---|---|
| O101 | 05-Jan-2026 | Laptop | 50000 |
| O102 | 10-Jan-2026 | Mouse | 12000 |
| O103 | 15-Jan-2026 | Laptop | 45000 |
| O104 | 20-Jan-2026 | Laptop | 30000 |
| O105 | 05-Feb-2026 | Laptop | 60000 |
| O106 | 25-Jan-2026 | Mouse | 15000 |
| O107 | 30-Jan-2026 | Laptop | 40000 |
Excel Formula
=SUMIFS(D2:D8,C2:C8,"Laptop",B2:B8,">="&DATE(2026,1,1),B2:B8,"<="&DATE(2026,1,31))
Expected Result
| Product | Date Range | Total Sales |
|---|---|---|
| Laptop | January 2026 | 165000 |
Explanation
Excel checks three conditions:
- Product must be Laptop.
- Order Date must be on or after 1 January 2026.
- Order Date must be on or before 31 January 2026.
Only Laptop sales within January 2026 are added.
Concept Covered: Date-range aggregation with multiple conditions.
Question 7: Calculate Sales Using Cell References
Problem Statement
A company wants a flexible sales report where the user can select a region and product from separate cells.
Sales Data
| Order ID | Region | Product | Sales |
|---|---|---|---|
| O101 | North | Laptop | 50000 |
| O102 | South | Mouse | 12000 |
| O103 | North | Laptop | 45000 |
| O104 | North | Keyboard | 18000 |
| O105 | South | Laptop | 60000 |
| O106 | North | Mouse | 10000 |
| O107 | North | Laptop | 40000 |
Enter the following criteria:
| Cell | Value |
|---|---|
| F2 | North |
| G2 | Laptop |
Excel Formula
=SUMIFS(D2:D8,B2:B8,F2,C2:C8,G2)
Expected Result
| Selected Region | Selected Product | Total Sales |
|---|---|---|
| North | Laptop | 135000 |
Explanation
The formula uses F2 as the region condition and G2 as the product condition. Change either cell to calculate a different combination of sales.
Concept Covered: Creating a flexible multi-condition report.
Student Practice Tasks
Use SUMIFS to solve these tasks:
- Calculate Laptop sales in the South region.
- Calculate February revenue in the North region.
- Calculate IT department expenses for January.
- Calculate sales generated by Neha in the North region.
- Calculate West region sales where individual transactions are greater than ₹25,000.
Final Student Challenge: Build a Multi-Condition Sales Report
A company maintains the following sales records. Create a summary report using SUMIFS.
Sales Data
| Order ID | Region | Product | Employee | Month | Sales |
|---|---|---|---|---|---|
| O101 | North | Laptop | Amit | January | 50000 |
| O102 | South | Mouse | Neha | January | 12000 |
| O103 | North | Laptop | Rahul | February | 45000 |
| O104 | East | Keyboard | Amit | January | 18000 |
| O105 | West | Laptop | Neha | February | 55000 |
| O106 | South | Laptop | Amit | March | 60000 |
| O107 | North | Mouse | Neha | January | 10000 |
| O108 | East | Laptop | Rahul | March | 40000 |
| O109 | West | Keyboard | Amit | January | 20000 |
| O110 | North | Laptop | Amit | March | 35000 |
| O111 | South | Keyboard | Rahul | February | 15000 |
| O112 | North | Laptop | Neha | January | 42000 |
Challenge Requirements
Prepare these reports:
1. Region and Product-Wise Sales
Calculate Laptop sales for each region.
2. Employee and Month-Wise Sales
Calculate Amit’s total sales for January.
3. Region and Month-Wise Revenue
Calculate total January revenue for the North region.
4. Multiple Condition Sales Report
Calculate Laptop sales in the North region generated by Amit.
Use SUMIFS for every calculation.
Key Takeaways
- SUMIFS adds values based on multiple conditions.
- Every specified condition must be satisfied for a row to be included.
- It is useful for region-wise, product-wise, employee-wise, and department-wise reports.
- It supports text, number, comparison, and date conditions.
- You can use cell references to create flexible reports.
- The sum range is written first in SUMIFS syntax.
Frequently Asked Questions (FAQs)
1. What is the SUMIFS function in Excel?
SUMIFS is an Excel function that adds values when multiple specified conditions are satisfied.
2. What is the syntax of SUMIFS?
The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, ...).
3. What is the difference between SUMIF and SUMIFS?
SUMIF is designed for a single condition, while SUMIFS can calculate totals using multiple conditions.
4. Can SUMIFS calculate region-wise and product-wise sales?
Yes. You can specify the region and product as separate conditions to calculate the matching sales total.
5. Can SUMIFS work with date ranges?
Yes. You can use greater-than-or-equal-to and less-than-or-equal-to conditions to calculate totals within a date range.
6. Can SUMIFS use cell references as criteria?
Yes. You can enter criteria in separate cells and refer to those cells in the formula.
7. Does SUMIFS require all conditions to match?
Yes. A row is included only when every specified condition is satisfied.
8. Can SUMIFS calculate values greater than a specific amount?
Yes. You can use criteria such as ">20000" to include only matching values greater than 20,000.

