A time comes when we want to sum data which is based on related criteria within the same worksheet. In Excel, there are two functions that allow for this, the Excel SUMIF Function and the Excel SUMIFS Function.
But as you might already be aware, the Excel SUMIF function will only apply if you want to apply a single criterion. But what if you have multiple criteria to apply on the same datasheet?
In the event that you have multiple criteria to apply, you need to use the Excel SUMIFS function. This function is effective in summing values of adjacent cells that meet the given criteria which can be based on dates, text as well as numbers. It is essential to note that the SUMIFS function can support logical operators such as >, <, <> and =. It can also support wildcards like * and ?. These logical operators are used for partial matching.
Understanding the excel SUMIFS arguments
- Sum_range- this specifies the range to be summed
- Range1- identifies the first range to be evaluated
- Criteria1- specifies the criteria to be used on range1
- Range2- this is optional and specifies the second range to be evaluated
- Criteria2– this is also optional, and it specifies the criteria to be used on the second range
For this formula, Excel will allow one to enter a maximum of 127 range and criteria pairs. For the formula to work correctly, the rows and columns used need to be same in criteria_range argument as well as sum_range argument.
The Excel SUMIFS function will return a numerical value as the result if the formula is used in the correct way.
Example: Flower data and its cost per region
For us to have a clear understanding of how the Excel SUMIFS function is used, we need to look at the following example:
Figure 1: Using Excel SUMIFS function
In the above dataset, let us say we want to find out the total cost of “Carnations” in “Indiana.” To do this, we proceed as follows:
State the formula
The above formula will look as in the diagram below:
Figure 2: Using Excel SUMIFS function
As you can see, the total cost of carnations in Indiana is 105. We have done this through the use of Excel’s SUMIFS function.
Things to remember
While using the Excel SUMIFS function, any non-numeric criteria need to be enclosed in double quotes. If, however, you are dealing with numeric criteria, then you do not need to use the double quotes unless it has logical operators. The Excel SUMIFS function, just like its SUMIF counterpart, can only handle ranges and not arrays.