Question: I have a sheet of sales information and would like to add together all lines for not only a specific product category, but for all sales of that product category in a particular region. How can I do this with one formula?
Answer: By using the SUMIFS function
Process (Excel 2007 and 2010):
Firstly, a note: MS Office 2010, including a new version of MS Excel, was officially released during the course of May, 2010. Going forward, we will be including Tips and Tricks that focus on the functionality available in Excel 2007/2010 that may not exist in Excel 2003. We will still include Tips that reference Excel 2003 functionality, but it is important to be aware of this difference in case you try to replicate an Excel 2007/2010 tip in Excel 2003 and not achieve the expected result.
Last week’s Tip covered the SUMIF function which allows you to total a number of lines together based on one common trait, such as a product name, a region or category, or a particular person’s name. However, this week’s tip looks at the situation where you may want to add values together based on two or more criteria, such as product name as well as a particular region.
The syntax of the SUMIFS function is as follows:
=SUMIFS(sum_range,criteria_range1,criteria1,…)
In the syntax, the sum_range is the column that contains the values you’d like to add together. Criteria_range1 is the range that you would like to search in for your first criteria value (criteria1), and this can be repeated using the same syntax (i.e. criteria_range2, criteria2, criteria_rangeX,criteriaX, …). You can include up to 127 different range/criteria pairings.
In this example, we’re going to use the following table of information. Column A contains all of the regions, with column C containing product categories. We will use the SUMIFS function to add the total product sales together (column G) for all beverage sales on the North Coast. We have highlighted the cells that will be added together in light green, to show which cells will be targeted.
- Select cell K2, type in the formula =SUMIFS(G:G,A:A,I2,C:C,J2) and press Enter.
This will search through column A for all “North Coast” references. It will then check whether the Category Name cell in that row (column C) contains the “Beverages” reference. If the row contains “North Coast” and “Beverages”, then the Product Sales field for that row will be added to the total.
You’ll note in the SUMIFS formula tip in the graphic that from criteria_range2 onwards, the parameters are in square brackets (e.g. [criteria_range2, criteria2]) – this means that the extra criteria past the first is optional. The SUMIFS function can be used then in place of SUMIF, which requires only one criteria, as everything from the second criteria onwards is not required.
NB: The SUMIFS function is not available in MS Excel 2003.
