Excel is a powerful tool that offers a wide range of functions to perform various calculations and data analysis tasks. One of the most commonly used functions in Excel is the SUMIF function, which allows you to add up values in a range based on specified criteria. In this blog post, we will explore how to use the SUMIF function with examples and data tables to help you master this handy feature.

What is the SUMIF Function?

The SUMIF function in Excel is a conditional summing function that allows you to add up values in a range based on a specified condition or criteria. It has the following syntax:

=SUMIF(range, criteria, [sum_range])

• range: This is the range of cells that you want to evaluate based on the specified criteria.
• criteria: This is the condition or criteria that you want to apply to the range. It can be a number, text, logical expression, or cell reference.
• sum_range (optional): This is the range of cells that you want to sum. If this parameter is omitted, Excel will use the range parameter as the sum range by default.

Now let’s dive into some examples and data tables to understand how the SUMIF function works in practice.

Example 1: Summing Values Based on a Single Criteria

Suppose you have a data table that contains the sales data of different products in a store, as shown below:

You want to calculate the total sales for Product A. You can use the SUMIF function to achieve this. In cell B8, enter the formula:

=SUMIF(A2:A7, “Product A”, B2:B7)

Explanation:

• A2:A7 is the range that contains the product names.
• "Product A" is the criteria that you want to apply to the range, which is the product name “Product A”.
• B2:B7 is the sum range that contains the sales data.

The result will be $370, which is the total sales for Product A.

Example 2: Summing Values Based on Multiple Criteria

The SUMIF function can also handle multiple criteria using logical operators such as “AND” and “OR”. Let’s consider the following data table that contains sales data for different products in different regions:

Suppose you want to calculate the total sales for Product A in the North and East regions. You can use the SUMIF function with multiple criteria as follows:

=SUMIF(A2:A7, “Product A”, C2:C7) + SUMIF(B2:B7, “North”, C2:C7) + SUMIF(B2:B7, “East”, C2:C7)

Explanation:

• A2:A7 is the range that contains the product names.
• "Product A" is the criteria for the product name “Product A”.
• B2:B7 is the range that contains the region names.
• "North" is the criteria

Example 3: Summing Values Based on Numeric Criteria

Let’s consider a data table that contains the scores of students in a class for a particular subject, as shown below:

Suppose you want to calculate the total scores of all students who scored above 90 in the subject of Math. You can use the SUMIF function with a numeric criteria as follows:

=SUMIFS(B2:B7, "Math", C2:C7, ">90")

Explanation:

• B2:B7 is the range that contains the subject names.
• "Math" is the criteria for the subject name “Math”.
• C2:C7 is the range that contains the scores.
• ">90" is the numeric criteria, specifying that you want to sum values greater than 90.

The result will be 192, which is the total score of all students who scored above 90 in Math.

Example 4: Summing Values Based on Partial Text Match

Consider a data table that contains the expenses incurred by an individual for various categories, as shown below:

Suppose you want to calculate the total expenses incurred for the category “Groceries”, regardless of the specific subcategories within it. You can use the SUMIF function with a partial text match as follows:

=SUMIF(A2:A7, "*Groceries*", B2:B7)

Explanation:

• A2:A7 is the range that contains the category names.
• "*Groceries*" is the criteria for the category name “Groceries”, with asterisks (*) used as wildcard characters to indicate a partial text match.
• B2:B7 is the range that contains the amounts.

The result will be $270, which is the total expenses incurred for the category “Groceries”.