You already know how to calculate sums, but what if you only want to total select items from a list or want to generate a separate list of subtotals without inserting subtotals in the middle of a list? What is you wanted to use some criteria to screen what gets added, such as sales over a certain value?
SUMIF is a great function that serves these purposes.
In our example, we have sales data by region and product. Let's first total it by region. Below the data, create a list of the different regions. Then we will total the sales by region, using the following SUMIF formula as shown in the formula in the worksheet below.
Lets look at components inside the formula:
Range- A$2:A$13. This is the range that contains the regions. A common mistake here is to put in the range for the data to be summed. That will come later. Notice that we use dollar signs to make the row references absolute. That way, we can copy the formula down for each region.
Criteria- A16. This is the value you are looking for in the range. When SUMIF sees this value in the range, it will add it to the running total for that region.
Sum_Range- C$2:C$13. Now we put in the range that contains the data that will be summed. Once again, we use dollar signs to make the rows absolute so we can copy the formula down to the other regions.
For those of you who have already learned about using named ranges, this will make the formulas even simpler. First, create three named ranges:
Region- covering the range of data plus the header for the Region field (cells A1:A13)
Product- similar for product
Sales- similar for sales
Then the SUMIF formula using named ranges would be:
=SUMIF(region,A16,sales)
Notice the benefit of using named ranges. The formula is simpler. You also don't have to worry about putting in the dollar signs to get the right row references. The formula is also more intuitive for your reader or for you to check.
As you can see, SUMIF can be pretty powerful. However, as with a lot of Excel functions, let's not just stop there. Let's go a bit further and show a more powerful use of SUMIF that many people may not realize.
Let's do some groupings of the sales results, by sales ranges. First, enter the sales ranges below the sales data. Then we will enter the SUMIF calculation, but this time with a different twist. Now, the sales data will be part of the range, as well as sum_range. We also will use a different form of criteria, with a formula rather than a link.
The new formula varies for each cell. For the range from 200-400 it is:
=SUMIF(C$2:C$13,"<400",C$2:C$13)-C16
Range. Now the sales data is the range
Criteria. Here we capture all the sales below the maximum value of the range.
Sum_Range. Once again, we repeat the sales data.
Additional calculation. With the "-C16", we back out all the sales in the prior ranges.
As often is the case with Excel, you can accomplish the same result multiple ways. Pivot tables are another way to accomplish this. SUMIF is a good way to do calculations like this when you want to use less memory, keep the file size smaller or keep a certain look the result.
In our example, we kept the result on the same sheet as the list. You can however, put the SUMIF formulas and results on a different sheet, such as a summary sheet. If you do that, it is another reason to use named ranges and make your formula building easier.
SUMIF is a great way to summarize information. Use what you learned here to use it in a number of creative ways.