A Quick Guide to Using the SUMIF in Excel – 2 Helpful Examples

Excel, a widely-used spreadsheet software, offers a multitude of powerful features for managing and analyzing data. One such feature that proves to be extremely useful is SUMIF. The SUMIF in Excel enables you to sum values in a range that satisfies a condition.

In this article, we will be covering the following topics in detail –

Let us look at each one of these topics one by one.

Download the Excel Workbook below to follow along and understand how to use SUMIF in Excel –
download excel workbookSUMIF-in-Excel.xlsx

 

Introduction to SUMIF in Excel

SUMIF is a wisely used Excel function that allows you to sum the values in a range that meets the criteria that you specify.

SUMIF in Excel is a flexible function that may be applied in a wide range of situations, from determining sales numbers for particular goods or geographical areas to examining client information based on demographics or purchase patterns.

So if you have a list of product IDs in one column and their Sales in another column and want to Sum the sales of only one of the product IDs, then the SUMIF function is the one for you.

 

SYNTAX of SUMIF

=SUMIF(Range, Criteria, [Sum_Range])

• Range – The range that is tested using criteria. Once items in the range are found, their corresponding values in sum_range are added. Required.
• Criteria – The criteria that define which cells in the range will be added. For example, criteria can be entered as 32, “>32”, B4, “apples”, or “32”. Required.
• [Sum_Range] – The range of cells to sum. Optional.

Let us look at an example to help us understand this function better.

 

Example of SUMIF function in Excel

Example 1

In his example, we want to get the sum of the sales amounts that are above $15,000. Follow the step-by-step tutorial below –

STEP 1: Enter the SUMIF function in cell F3.

=SUMIF

STEP 2: Enter the first argument – range. Here we have selected the range C2:C89 as it contains all the sales amount.

=SUMIF(C2:C89,

STEP 3: Enter the second argument – criteria. Here we have entered “>15000” as we want to get the sum of sales where the sales amount is greater than $15,000.

=SUMIF(C2:C89,”>15000″,

Below we can see, the SUMIF function returns the sum of sales where the sales amount is greater than $15,000.

 

Example 2

Here, we will be using the optional argument – sum_range as well. The sum_range argument is used when you want to sum values from a different range than the one used for the criteria. If your criteria are based on one column, but you want to sum values from a different column, you would use the sum_range argument.

For example, if you have a list of products in column A and their corresponding sales amounts in column C, and you want to sum the sales amounts for a specific product (Product ID – 1001), you would use the sum_range argument to specify column C as the range to sum from.

Follow the step-by-step tutorial below –

STEP 1: Enter the SUMIF function in cell F3.

=SUMIF

STEP 2: Enter the first argument – range. Here we have selected the range A2:A89 as it contains all the product IDs.

=SUMIF(A2:A89,

STEP 3: Enter the second argument – criteria. Here we have entered “1001” as we want the sum of sales for product ID 1001.

=SUMIF(A2:A89,”1001″,

STEP 4: Enter the third argument – sum_range. Here we have selected the C2:C89 range as it contains all the sales amount.

=SUMIF(A2:A89,”1001″,C2:C89)

The SUMIF function calculates and provides the sum of sales for product 1001.

 

Restrictions of using SUMIF

Even though the SUMIF function is extremely useful, it has some restrictions that you should be aware of:

• Range size: The SUMIF function may not be appropriate for some complicated data analysis scenarios since it can become slow or cumbersome when employed with very big data sets.
• Syntax difficulty: Using the SUMIF function might be challenging, especially if you’re juggling intricate data sets. To utilize it properly, one must have a thorough understanding of Excel’s features and syntax.
• The size of each range must be uniform. A #VALUE error will be returned if the supplied ranges don’t match.
• All range arguments must be actual ranges; an array cannot be used with the SUMIF function.
• SUMIF is not case-sensitive.

 

Conclusion

The article introduces the SUMIF function in Excel, which is used to sum values in a range based on specific criteria. It explains the syntax of the function, consisting of the range, criteria, and optional sum_range arguments. It also provides two examples demonstrating how to use SUMIF in Excel. It also highlights some restrictions on using this function.