SUMIF Function in Excel Tutorial

Excel Tutorial

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 IDRegionSales
O101North15000
O102South12000
O103North18000
O104East10000
O105South14000
O106North20000

Excel Formula

=SUMIF(B2:B7,"North",C2:C7)

Expected Result

RegionTotal Sales
North53000

Explanation

  • B2:B7 contains the region names.
  • "North" is the condition.
  • C2:C7 contains 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

ProductQuantityRevenue
Laptop5250000
Mouse2010000
Keyboard1015000
Laptop3150000
Mouse157500
Laptop2100000

Excel Formula

=SUMIF(A2:A7,"Laptop",C2:C7)

Expected Result

ProductTotal Revenue
Laptop500000

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 IDDepartmentExpense Amount
E101HR12000
E102IT25000
E103Sales18000
E104HR15000
E105IT30000
E106HR10000

Excel Formula

=SUMIF(B2:B7,"HR",C2:C7)

Expected Result

DepartmentTotal Expenses
HR37000

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 IDMonthRevenue
INV101January45000
INV102February38000
INV103January52000
INV104March41000
INV105February30000
INV106January60000

Excel Formula

=SUMIF(B2:B7,"January",C2:C7)

Expected Result

MonthTotal Revenue
January157000

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

EmployeeRegionSales
AmitNorth25000
NehaSouth30000
AmitEast18000
RahulWest22000
NehaNorth15000
AmitSouth27000

Excel Formula

=SUMIF(A2:A7,"Amit",C2:C7)

Expected Result

EmployeeTotal Sales
Amit70000

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 IDSales
O10115000
O10225000
O10335000
O10418000
O10540000
O10612000

Excel Formula

=SUMIF(B2:B7,">20000")

Expected Result

ConditionTotal Sales
Greater than 20000100000

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 IDRegionSales
O101North15000
O102South12000
O103North18000
O104East10000
O105South14000
O106North20000

Enter North in E2.

Excel Formula

=SUMIF(B2:B7,E2,C2:C7)

Expected Result

Selected RegionTotal Sales
North53000

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:

  1. Calculate total sales for the West region.
  2. Calculate total revenue generated by Mouse.
  3. Calculate total expenses for the IT department.
  4. Calculate total revenue for February.
  5. 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 IDRegionProductSales
O101NorthLaptop50000
O102SouthMouse12000
O103EastLaptop45000
O104NorthKeyboard18000
O105WestMouse15000
O106SouthLaptop60000
O107NorthMouse10000
O108EastKeyboard20000
O109WestLaptop55000
O110NorthLaptop40000

Challenge Requirements

Prepare these three summary reports:

1. Region-Wise Sales

RegionTotal Sales
North
South
East
West

2. Product-Wise Sales

ProductTotal 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.