In Excel, it’s common to want to add cells that meet a specific criterion. This task might seem daunting or insurmountable. But don’t worry, Excel offers a way to save considerable time with its SUMIF function.
In this article, we’ll walk you through some nuances of this function so that you can be more comfortable and efficient in Excel.
Definition
First and foremost, it’s important to understand that the SUMIF function in Excel is a combination of two functions: the SUM function and the IF function. The former is used to add a range or series of data, such as the total on an invoice. On the other hand, the IF function is used to display results based on chosen conditions, for example, displaying a name only if the result exceeds 50%.
The Excel SUMIF function, therefore, calculates the sum of all values in a data range that meet a specific criterion of your choice. It’s as if you want to add up the prices of all items in the fruit aisle on a shopping list. Your formula would only consider items within the fruit category.
Formula
The formula for the SUMIF function in Excel is as follows:
= SUMIF(Range, Criteria, Sum_Range)
Range: This is the range you want to evaluate. In other words, it’s the data range you want to add based on the chosen criterion. Empty or text values are not taken into account.
Criteria: This can be a cell directly containing your criterion or a value you enter directly into the formula.
Wildcard characters can be included in your criteria. For instance, a question mark “?” represents a single character, while an asterisk “*” refers to a sequence of characters.
If you’re looking for a specific number, you can use “=15” or simply “15”. This will sum all cells containing the value 15.
The same applies to text. If you’re looking for an exact text match, simply enter “bio”. Excel will sum all cells containing the exact text “bio”.
Numeric comparisons can also be made. For example, to sum cells containing a value less than 20, enter “<20”. The same logic applies for greater values: “>20”.
You can use an existing cell as a criterion. For example, if it’s cell C18, simply enter “C18”. Excel will then calculate the sum of all cells containing the same value as cell C18.
Sum_Range: This is typically the data range used for the sum calculation. This applies when the “amounts” cell is in a different data range.
I hope this translation is helpful! If you have any further questions, feel free to ask.
