The SUMIFS function in Excel is used to sum a range of values based on multiple criteria. It's particularly useful for financial analysis, data analysis, and any scenario where you need to sum data conditionally.

Syntax

Parameters

• sum_range: The range of cells to sum.
• criteria_range1: The range of cells that you want to apply the first criteria against.
• criteria1: The condition that must be met in criteria_range1.
• criteria_range2, criteria2: (Optional) Additional ranges and criteria. You can include multiple pairs of ranges and criteria.

Example Scenario

Consider the following sales data in Excel (A1

):

Goal

You want to sum the total sales of Widget A in the North region.

Steps

1. Identify the Ranges and Criteria:

   • sum_range: C2
      
     (Sales)
   • criteria_range1: B2
      
     (Product)
   • criteria1: "Widget A"
   • criteria_range2: D2
      
     (Region)
   • criteria2: "North"

2. Write the SUMIFS Formula:

    

   =SUMIFS(C2:C6, B2:B6, "Widget A", D2:D6, "North")

Explanation

• C2
   
  : This is the range containing the values you want to sum (Sales).
• B2
   
  : This range is checked against the first criterion ("Widget A").
• D2
   
  : This range is checked against the second criterion ("North").

Result

This formula will return 400, as it sums the sales of Widget A in the North region (150 + 250).

Additional Example

Scenario

You want to sum total sales for Widget B in the South region.

1. Formula:
    

   =SUMIFS(C2:C6, B2:B6, "Widget B", D2:D6, "South")

Explanation

• This will return 300, as it sums the sales of Widget B in the South region (200 + 100).

Important Notes

• Multiple Criteria: You can include multiple pairs of criteria ranges and criteria to refine your sums further.
• Non-Contiguous Ranges: SUMIFS only works with contiguous ranges for sum_range and criteria_range.
• Criteria can be Cell References: Instead of hardcoding criteria like "Widget A," you can reference another cell (e.g., =SUMIFS(C2:C6, B2:B6, E1, D2:D6, "North"), where E1 contains "Widget A").