How to Use the SUMIFS Function in Excel

Excel provides powerful tools for analyzing and managing data, and one of the most useful functions is the SUMIFS function. This guide will walk you through everything you need to know about using the SUMIFS function effectively, including what it is, where it can be applied, and how to use it with live examples.

What is the SUMIFS Function?

The SUMIFS function in Excel adds up the values in a range that meet multiple criteria. Unlike the SUMIF function, which evaluates only one condition, SUMIFS allows you to specify multiple conditions across one or more ranges.

Syntax of the SUMIFS Function:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range: The range of cells to sum.
• criteria_range1: The first range of cells to evaluate.
• criteria1: The condition that must be met for the first range.
• [criteria_range2, criteria2]: (Optional) Additional ranges and their corresponding conditions.

You can add as many range-criteria pairs as needed, but each range must have the same number of rows and columns.

Where Can We Use the SUMIFS Function?

The SUMIFS function is versatile and can be used in various scenarios, such as:

1. Sales Analysis: Sum sales for a specific product in a specific region during a specific time period.
2. Survey Results: Sum scores for responses matching multiple conditions (e.g., “Yes” answers from a specific age group).
3. Attendance Tracking: Sum hours worked by employees in a specific department during a specific month.
4. Inventory Management: Sum quantities of items below a certain stock level in a specific category.
5. Performance Metrics: Evaluate total sales for employees who meet multiple performance criteria (e.g., high sales and low errors).

How to Use the SUMIFS Function in Different Methods

There are several ways to use the SUMIFS function in Excel. Below are four common methods:

Method 1: Summing Cells with Multiple Criteria

This method sums values in a range that meet multiple conditions across different ranges.

Steps:

1. Open Excel and enter your data into a worksheet. For example:

   A1: Apple
   A2: Banana
   A3: Apple
   A4: Orange
   B1: 10
   B2: 20
   B3: 30
   B4: 40
   C1: Red
   C2: Yellow
   C3: Green
   C4: Orange

2. Click on the cell where you want the result (e.g., D1).
3. Type the formula:

   =SUMIFS(B1:B4, A1:A4, "Apple", C1:C4, "Green")

4. Press Enter. The result (30) will appear in cell D1.

Live Example:

Column A	Column B	Column C	Formula	Result
Apple	10	Red	=SUMIFS(B1:B4, A1:A4, “Apple”, C1:C4, “Green”)	30
Banana	20	Yellow
Apple	30	Green
Orange	40	Orange

Method 2: Using Logical Operators

You can use logical operators like >, <, >=, <=, and <> to define conditions.

Steps:

1. Enter your data into a column or row. For example:

   D1: 50
   D2: 60
   D3: 70
   D4: 80
   E1: 100
   E2: 120
   E3: 130
   E4: 140

2. In the target cell, type the formula with multiple conditions:

   =SUMIFS(E1:E4, D1:D4, ">60", E1:E4, "<=130")

3. Press Enter. The result (250) will appear.

Live Example:

Column D	Column E	Formula	Result
50	100	=SUMIFS(E1:E4, D1:D4, “>60”, E1:E4, “<=130”)	250
60	120
70	130
80	140

Method 3: Using Wildcards for Partial Matches

You can use wildcards (* and ?) to sum cells with partial matches.

• * matches any sequence of characters.
• ? matches a single character.

Steps:

1. Enter your data into a column. For example:

   F1: Apple
   F2: Applesauce
   F3: Pineapple
   F4: Grape
   G1: 10
   G2: 20
   G3: 30
   G4: 40

2. In the target cell, type the formula with a wildcard:

   =SUMIFS(G1:G4, F1:F4, "Apple*", G1:G4, ">15")

3. Press Enter. The result (50) will appear.

Live Example:

Column F	Column G	Formula	Result
Apple	10	=SUMIFS(G1:G4, F1:F4, “Apple*”, G1:G4, “>15”)	50
Applesauce	20
Pineapple	30
Grape	40

Method 4: Using Cell References for Criteria

You can make the SUMIFS function dynamic by referencing cells for the criteria.

Steps:

1. Enter your data into a column. For example:

   H1: 50
   H2: 60
   H3: 70
   H4: 80
   I1: 100
   I2: 120
   I3: 130
   I4: 140
   J1: >60
   J2: <=130

2. In the target cell, type the formula:

   =SUMIFS(I1:I4, H1:H4, J1, I1:I4, J2)

3. Press Enter. The result will dynamically update based on the values in J1 and J2.

Live Example:

Column H	Column I	J1	J2	Formula	Result
50	100	>60	<=130	=SUMIFS(I1:I4, H1:H4, J1, I1:I4, J2)	250
60	120
70	130
80	140

Live Example Showing Progress in Each Step

Let’s work through a complete example step-by-step:

Data Setup:

Column A	Column B	Column C	Column D
Apple	10	Red	5
Banana	20	Yellow	15
Apple	30	Green	25
Orange	40	Orange	35

Goal:

Sum the values in Column B where:

1. Column A is "Apple".
2. Column D is greater than 10.

Step 1: Enter the Data

Fill in the values in columns A, B, C, and D.

Step 2: Use the SUMIFS Function

Click on cell E1 and type:

=SUMIFS(B1:B4, A1:A4, "Apple", D1:D4, ">10")

Step 3: Press Enter

After pressing Enter, the result (30) will appear in cell E1.

Final Table:

Column A	Column B	Column C	Column D	Formula	Result
Apple	10	Red	5	=SUMIFS(B1:B4, A1:A4, “Apple”, D1:D4, “>10”)	30
Banana	20	Yellow	15
Apple	30	Green	25
Orange	40	Orange	35

Key Notes About the SUMIFS Function

1. Multiple Criteria: You can add as many range-criteria pairs as needed, but each range must have the same dimensions.
2. Logical Operators: Use operators like >, <, >=, <=, and <> to define conditions.
3. Case-Insensitive Matching: The SUMIFS function is case-insensitive when evaluating text criteria.
4. Dynamic Criteria: Use cell references for criteria to make the function dynamic and reusable.

Conclusion

The SUMIFS function is a powerful tool in Excel for performing conditional summation with multiple criteria. Whether you’re analyzing survey responses, tracking inventory, or evaluating sales performance, the SUMIFS function simplifies the process of summing data based on complex conditions.

By mastering these methods, you’ll be able to handle a wide variety of tasks, from basic summation to advanced data analysis. Practice using the SUMIFS function with real-world datasets to become proficient!