Introduction
The SUMIF function in Excel helps you add numbers based on a specific condition. It is useful for preparing region-wise sales reports, product-wise revenue summaries, and department-wise expense reports. Instead of manually selecting and adding values, you can use SUMIF to calculate totals automatically based on your chosen criteria.
What is the SUMIF Function in Excel?
SUMIF is a conditional aggregation function that adds values only when they meet a specified condition.
For example:
- Calculate total sales for the North region.
- Calculate revenue generated by a particular product.
- Calculate expenses for the HR department.
- Calculate sales made by a specific employee.
SUMIF Function Syntax
=SUMIF(range, criteria, [sum_range])
Arguments:
range: The cells Excel checks against your condition.criteria: The condition that must be satisfied.sum_range: The cells containing the numbers to add. This argument is optional.
Practical Examples of SUMIF Function in Excel
Question 1: Calculate Region-Wise Sales
Problem Statement
A company maintains sales records for different regions. Calculate the total sales generated by the North region.
Sales Data
| Order ID | Region | Sales |
|---|---|---|
| O101 | North | 15000 |
| O102 | South | 12000 |
| O103 | North | 18000 |
| O104 | East | 10000 |
| O105 | South | 14000 |
| O106 | North | 20000 |
Excel Formula
=SUMIF(B2:B7,"North",C2:C7)
Expected Result
| Region | Total Sales |
|---|---|
| North | 53000 |
Explanation
B2:B7contains the region names."North"is the condition.C2:C7contains the sales amounts.
Excel adds sales only for records belonging to the North region.
Concept Covered: Region-wise sales aggregation.
Question 2: Calculate Product-Wise Revenue
Problem Statement
A retail company sells different products. Calculate the total revenue generated by Laptop sales.
Sales Data
| Product | Quantity | Revenue |
|---|---|---|
| Laptop | 5 | 250000 |
| Mouse | 20 | 10000 |
| Keyboard | 10 | 15000 |
| Laptop | 3 | 150000 |
| Mouse | 15 | 7500 |
| Laptop | 2 | 100000 |
Excel Formula
=SUMIF(A2:A7,"Laptop",C2:C7)
Expected Result
| Product | Total Revenue |
|---|---|
| Laptop | 500000 |
Explanation
Excel checks the Product column and adds the Revenue values wherever the product name is Laptop.
Concept Covered: Product-wise revenue calculation.
Question 3: Calculate Department-Wise Expenses
Problem Statement
An organization maintains expense records for different departments. Calculate the total expenses of the HR department.
Expense Data
| Expense ID | Department | Expense Amount |
|---|---|---|
| E101 | HR | 12000 |
| E102 | IT | 25000 |
| E103 | Sales | 18000 |
| E104 | HR | 15000 |
| E105 | IT | 30000 |
| E106 | HR | 10000 |
Excel Formula
=SUMIF(B2:B7,"HR",C2:C7)
Expected Result
| Department | Total Expenses |
|---|---|
| HR | 37000 |
Explanation
Excel checks the Department column for HR and adds the corresponding Expense Amount values.
Concept Covered: Department-wise expense reporting.
Question 4: Calculate Month-Wise Revenue
Problem Statement
A company wants to calculate the total revenue generated in January.
Revenue Data
| Invoice ID | Month | Revenue |
|---|---|---|
| INV101 | January | 45000 |
| INV102 | February | 38000 |
| INV103 | January | 52000 |
| INV104 | March | 41000 |
| INV105 | February | 30000 |
| INV106 | January | 60000 |
Excel Formula
=SUMIF(B2:B7,"January",C2:C7)
Expected Result
| Month | Total Revenue |
|---|---|
| January | 157000 |
Explanation
Excel checks the Month column and adds the revenue from all January records.
Concept Covered: Month-wise revenue aggregation.
Question 5: Calculate Sales for a Specific Employee
Problem Statement
A sales manager wants to calculate the total sales generated by employee Amit.
Employee Sales Data
| Employee | Region | Sales |
|---|---|---|
| Amit | North | 25000 |
| Neha | South | 30000 |
| Amit | East | 18000 |
| Rahul | West | 22000 |
| Neha | North | 15000 |
| Amit | South | 27000 |
Excel Formula
=SUMIF(A2:A7,"Amit",C2:C7)
Expected Result
| Employee | Total Sales |
|---|---|
| Amit | 70000 |
Explanation
Excel checks the Employee column for Amit and adds all matching sales amounts.
Concept Covered: Employee-wise sales reporting.
Question 6: Calculate Sales Greater Than a Specific Amount
Problem Statement
A company wants to calculate the total value of all sales transactions greater than ₹20,000.
Sales Data
| Order ID | Sales |
|---|---|
| O101 | 15000 |
| O102 | 25000 |
| O103 | 35000 |
| O104 | 18000 |
| O105 | 40000 |
| O106 | 12000 |
Excel Formula
=SUMIF(B2:B7,">20000")
Expected Result
| Condition | Total Sales |
|---|---|
| Greater than 20000 | 100000 |
Explanation
Here, the sales values are both the condition range and the sum range. Excel adds only the values greater than ₹20,000.
Concept Covered: SUMIF with a greater-than condition.
Question 7: Calculate Sales Using a Cell Reference
Problem Statement
A company wants to calculate region-wise sales using a region name entered in a separate cell. This makes the report easier to update.
Sales Data
| Order ID | Region | Sales |
|---|---|---|
| O101 | North | 15000 |
| O102 | South | 12000 |
| O103 | North | 18000 |
| O104 | East | 10000 |
| O105 | South | 14000 |
| O106 | North | 20000 |
Enter North in E2.
Excel Formula
=SUMIF(B2:B7,E2,C2:C7)
Expected Result
| Selected Region | Total Sales |
|---|---|
| North | 53000 |
Explanation
Instead of typing the region name directly in the formula, Excel uses the value entered in E2. Change E2 to South or East to calculate the sales for that region.
Concept Covered: Using a cell reference as the SUMIF condition.
Student Practice Tasks
Use the SUMIF function to solve these tasks:
- Calculate total sales for the West region.
- Calculate total revenue generated by Mouse.
- Calculate total expenses for the IT department.
- Calculate total revenue for February.
- Calculate the total value of sales transactions greater than ₹30,000.
Final Student Challenge: Build a Sales Summary Report
A company maintains the following sales records. Create a summary report using SUMIF.
Sales Data
| Order ID | Region | Product | Sales |
|---|---|---|---|
| O101 | North | Laptop | 50000 |
| O102 | South | Mouse | 12000 |
| O103 | East | Laptop | 45000 |
| O104 | North | Keyboard | 18000 |
| O105 | West | Mouse | 15000 |
| O106 | South | Laptop | 60000 |
| O107 | North | Mouse | 10000 |
| O108 | East | Keyboard | 20000 |
| O109 | West | Laptop | 55000 |
| O110 | North | Laptop | 40000 |
Challenge Requirements
Prepare these three summary reports:
1. Region-Wise Sales
| Region | Total Sales |
|---|---|
| North | |
| South | |
| East | |
| West |
2. Product-Wise Sales
| Product | Total Sales |
|---|---|
| Laptop | |
| Mouse | |
| Keyboard |
3. Sales Above ₹30,000
Calculate the total value of all transactions greater than ₹30,000.
Try to complete all three reports using SUMIF formulas.
Key Takeaways
- SUMIF adds values based on a single condition.
- It is useful for region-wise sales and product-wise revenue reports.
- It can calculate department-wise expenses and month-wise revenue.
- You can use text, numbers, comparison conditions, and cell references as criteria.
- The sum range is optional when the condition range itself contains the numbers to add.
Frequently Asked Questions (FAQs)
1. What is the SUMIF function in Excel?
SUMIF is a conditional aggregation function that adds values matching a specified condition.
2. What is the syntax of SUMIF?
The syntax is =SUMIF(range, criteria, [sum_range]).
3. Can SUMIF calculate region-wise sales?
Yes. You can use the region column as the criteria range and the sales column as the sum range.
4. Can SUMIF work with text conditions?
Yes. You can use conditions such as "North", "Laptop", or "HR".
5. Can SUMIF calculate values greater than a specific amount?
Yes. You can use a condition such as ">20000" to add values greater than 20,000.
6. What is the difference between range and sum_range?
Range contains the cells Excel checks against the condition. Sum_range contains the numbers Excel adds when the condition is satisfied.
7. Is sum_range compulsory in SUMIF?
No. It is optional. If omitted, Excel adds the matching cells from the range itself.
8. Can SUMIF use a cell reference as criteria?
Yes. You can enter a condition in a cell and use that cell reference in the SUMIF formula.

