SUMIFS Function in Excel Tutorial

Excel Tutorial

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 IDRegionProductSales
O101NorthLaptop50000
O102SouthMouse12000
O103NorthLaptop45000
O104NorthKeyboard18000
O105SouthLaptop60000
O106NorthMouse10000
O107NorthLaptop40000

Excel Formula

=SUMIFS(D2:D8,B2:B8,"North",C2:C8,"Laptop")

Expected Result

RegionProductTotal Sales
NorthLaptop135000

Explanation

  • D2:D8 contains the sales amounts.
  • B2:B8 checks the region.
  • "North" is the first condition.
  • C2:C8 checks 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 IDMonthRegionRevenue
INV101JanuarySouth45000
INV102JanuaryNorth38000
INV103FebruarySouth52000
INV104JanuarySouth30000
INV105FebruaryNorth41000
INV106JanuarySouth25000
INV107MarchSouth60000

Excel Formula

=SUMIFS(D2:D8,B2:B8,"January",C2:C8,"South")

Expected Result

MonthRegionTotal Revenue
JanuarySouth100000

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 IDDepartmentMonthExpense
E101HRJanuary12000
E102ITJanuary25000
E103HRFebruary15000
E104HRJanuary18000
E105SalesJanuary20000
E106HRMarch10000
E107HRJanuary14000

Excel Formula

=SUMIFS(D2:D8,B2:B8,"HR",C2:C8,"January")

Expected Result

DepartmentMonthTotal Expenses
HRJanuary44000

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

EmployeeRegionProductSales
AmitNorthLaptop50000
NehaSouthMouse12000
AmitEastLaptop30000
RahulNorthKeyboard18000
AmitNorthMouse20000
NehaNorthLaptop40000
AmitNorthLaptop35000

Excel Formula

=SUMIFS(D2:D8,A2:A8,"Amit",B2:B8,"North")

Expected Result

EmployeeRegionTotal Sales
AmitNorth105000

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 IDRegionSales
O101North15000
O102South25000
O103North35000
O104North18000
O105North40000
O106South30000
O107North25000

Excel Formula

=SUMIFS(C2:C8,B2:B8,"North",C2:C8,">20000")

Expected Result

RegionSales ConditionTotal Sales
NorthGreater than 20000100000

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 IDOrder DateProductSales
O10105-Jan-2026Laptop50000
O10210-Jan-2026Mouse12000
O10315-Jan-2026Laptop45000
O10420-Jan-2026Laptop30000
O10505-Feb-2026Laptop60000
O10625-Jan-2026Mouse15000
O10730-Jan-2026Laptop40000

Excel Formula

=SUMIFS(D2:D8,C2:C8,"Laptop",B2:B8,">="&DATE(2026,1,1),B2:B8,"<="&DATE(2026,1,31))

Expected Result

ProductDate RangeTotal Sales
LaptopJanuary 2026165000

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 IDRegionProductSales
O101NorthLaptop50000
O102SouthMouse12000
O103NorthLaptop45000
O104NorthKeyboard18000
O105SouthLaptop60000
O106NorthMouse10000
O107NorthLaptop40000

Enter the following criteria:

CellValue
F2North
G2Laptop

Excel Formula

=SUMIFS(D2:D8,B2:B8,F2,C2:C8,G2)

Expected Result

Selected RegionSelected ProductTotal Sales
NorthLaptop135000

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:

  1. Calculate Laptop sales in the South region.
  2. Calculate February revenue in the North region.
  3. Calculate IT department expenses for January.
  4. Calculate sales generated by Neha in the North region.
  5. 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 IDRegionProductEmployeeMonthSales
O101NorthLaptopAmitJanuary50000
O102SouthMouseNehaJanuary12000
O103NorthLaptopRahulFebruary45000
O104EastKeyboardAmitJanuary18000
O105WestLaptopNehaFebruary55000
O106SouthLaptopAmitMarch60000
O107NorthMouseNehaJanuary10000
O108EastLaptopRahulMarch40000
O109WestKeyboardAmitJanuary20000
O110NorthLaptopAmitMarch35000
O111SouthKeyboardRahulFebruary15000
O112NorthLaptopNehaJanuary42000

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.