How to use SUMIFS in Excel | Easy Beginner's Tutorial

In this beginner-friendly tutorial, Microsoft Certified Trainer Allison Gonzalez from Pragmatic Works walks viewers through the functionality and versatility of the SUMIFS function in Microsoft Excel. This tool is incredibly useful when summing data based on multiple conditions, making it a must-know feature for anyone working with spreadsheets and datasets.

What is the SUMIFS Function?

The SUMIFS function calculates the total of a numeric range based on one or more conditions. Unlike the simpler SUMIF, SUMIFS supports up to 127 condition pairs, making it more robust and flexible.

Syntax Breakdown

The syntax for the SUMIFS function is:

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

Where:

• sum_range: The cells to be added.
• criteria_range: The range to evaluate for a condition.
• criteria: The condition to apply.

Allison suggests using SUMIFS by default—even for single-condition calculations—because its structure is consistent and scalable for future needs.

Example 1: Sum Products Starting with "A" Sold by Angelica

To demonstrate how SUMIFS works, Allison uses a sample dataset containing fall-themed products (e.g., apples, pumpkins) sold by herself and a colleague named Angelica.

1. Set sum_range to the "Quantity Sold" column (e.g., C14:C21).
2. First criteria_range: The "Product" column (e.g., D14:D21).
3. criteria: Use a wildcard condition "A*" to find products starting with “A”.
4. Second criteria_range: The "Salesperson" column (e.g., E14:E21).
5. criteria: "Angelica"

This returns the total quantity of products starting with "A" that Angelica sold, e.g., 3 apples.

Example 2: Dynamic Modifications

Allison shows how easily this formula can be modified:

• Change "A*" to "P*" to count pumpkins.
• Switch salesperson from "Angelica" to "Allison".
• Search for all products starting with "S" (e.g., scarecrows and sunflowers).

This dynamic nature allows users to analyze data across multiple dimensions without rewriting the entire formula.

Example 3: Excluding Criteria

The tutorial also covers how to exclude a specific condition. For example, to sum all products sold by Allison except apples, use:

1. sum_range: Quantity column (C14:C21)
2. criteria_range1: Salesperson column (E14:E21)
3. criteria1: "Allison"
4. criteria_range2: Product column (D14:D21)
5. criteria2: "<>Apples" (not equal to Apples)

This returns a total of all non-apple products sold by Allison.

Best Practices & Final Thoughts

• Use the formula bar instead of writing directly in cells to clearly view and manage your formula.
• SUMIFS is structurally different from SUMIF, but more scalable.
• You can use wildcards like * for partial text matching and <> for exclusions.

Allison encourages viewers to explore this function and tailor it to their own datasets. It's a powerful Excel skill that saves time and enhances data analysis capabilities.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.