Excel SUMIF: Using This Function to Calculate Sales of a Particular Item

The Microsoft suite of applications is still considered the standard for business and private use when it comes to word-processing and spreadsheets and with the launch of Microsoft 2013, their market share should increase due to the power of cloud services that are now included in their applications as well as the cheaper subscription based services Microsoft now offer. For a glimpse into the Cloud capabilities of the new Microsoft Office suite, sign up for the Office 2013 For Dummies Video Training, Deluxe Edition course from Udemy today.

Microsoft Excel has remained one of the most powerful spreadsheet applications on the market and the following tutorial will illustrate the power of using the SUMIF formula in your spreadsheets.

For the purposes of this tutorial, we will make use of a sample spreadsheet that contains sales data from a fictitious shoe store. The data contains sales information for various types, sizes and colors of shoes. To benefit from the tutorial, you need to have a good basic understanding of how to create a worksheet and how to use the basic functions of Excel. For a great introduction to Excel, sign up for the Excel 2013: Introducing Excel course today from Udemy or read Excel Tips and Tricks: Use Excel like a Pro.

This is our sample spreadsheet:

How to Work with Formulas in Excel

To use a formula in Excel you need to understand the syntax of the formula. Syntax is a technical term for how the formula is structured. In other words you need to learn to talk to Excel in a language it understands.

When you tell Excel to use SUMIF, Excel knows to add all the numbers in a given range depending on a condition you specify in the formula. To learn more about working with cells and ranges, read Excel Tutorial: An In-Depth Guide to Working with your Data, Building Formulas, and Using Functions.

The syntax for the SUMIF formula is expressed as:

SUMIF (range, condition, [sum_range])

The SUMIF calls the formula – in other words it tells Excel that it must perform the SUMIF calculation. The range part of the formula is required to tell Excel which range to use in its calculation of SUMIF. The condition criterion specifies the criteria Excel must use in the calculation.

The sum range is an optional criterion so you do not have to include this criteria in your calculations if you do not want to.

Let’s do a few examples using our example spreadsheet to show you how this works.

How to Use SUMIF

Let us use SUMIF with the range and condition criteria to sum the shoes for all size six shoes.

To use a formula in Excel you need to enter the formula you want to use into a cell. There are two ways to enter a formula into a cell. You can type the formula into the cell directly or you can select the cell and then select the formula from the formula tab. For our first example, we will use the formula tab to enter the formula. If you are not sure how to enter formulas in Excel, sign up for the Excel 2013 course from Udemy now.

Select E13 for our result and then click the formula tab. The formula tab contains most of the formulas available in Excel and the advantage of using the formula tab is that the formula tab also provides you with a wizard to help you complete your formula. SUMIF is part of the Math & Trig formulas for Excel. To Enter the SUMIF formula in C13, click Math & Trig and select the SUMIF formula:

Now you can use the wizard to complete the formula. Select the range for the SUMIF calculation by selecting the range criteria and then highlighting the Size column. Then enter 6 in the criteria tab so that Excel knows to add any numbers it finds in the range that are equal to 6.

The result of the above formula will look like this:

The above example shows you how to use the SUMIF function without using a second range as the target range.

Let’s use another example, but this time we want to know how many size 6 shoes we sold in January. So we want to tell Excel to add all of the sales in January for shoes that were size 6.

This example would use all three criteria of the SUMIF function.

To find out how many size six shoes we sold in January, select the cell you want the answer to appear in, and then select the SUMIF function from the Formula tab under Math & Trig. The range we want Excel to search through for size six shoes is C5:C10. We want Excel to search for “6” to indicate size 6 shoes, but this time we want Excel to add together all the sales in the January Sales column for size six shoes.

The formula will therefore look like this:

And the resultant worksheet will look like this:

The SUMIF formula also works with named ranges. To learn how to name your ranges in Excel, sign up for the Mastering MS Excel course now and learn Excel in a weekend.

In our final example, we have named the size range “ShoeSize”. To use the formula on the named range, you select the target cell for the formula, select the SUMIF formula from the Math & Trig tab and then enter the name of the range instead of the range itself:

The result would look like this: 

Excel is an incredibly powerful application when it comes to extracting and working with data and figures. To learn to harness of the power of Excel in your job or at home, sign up for the Excel Essentials course today and learn how to get the most out of this incredible application.