If you have a condition on top of summing up the values in a given range then you can come up with any criterion, and then be able to get the summation of the values as specified by your criteria. The “sum, if cells are equal to” procedure, will help you get the subtotal of a specific variable within a range of values. The Excel SUM function, on the other hand, will only help you get the total of all the values in the range, without helping you to get the total of values that meet certain criteria.
The general formula that can help us to sum if cells are equal to specific criteria is as below:
=SUMIF(range, criteria, [sum_range])
- The range is a required argument where you want to test your criteria.
- Criteria argument contains the condition or criterion you specify to test.
- [Sum_range] is optional argument. It contains numeric values that need to sum up if corresponding cell value meets the specified criteria in the range argument. If you omit this argument, function sums the values in range argument that meet the criteria.
There are times when we need to add up cells that meet certain criteria in Excel. We cannot do this using the SUM function, as this will just sum up all the cells in a given range. To sum up, cells that meet specific criteria in a range, we need to use the SUMIF function.
There are basically two scenarios when the SUMIF function is used. These areas stipulated below:
- When you want to sum up all cells in a range that meet set criteria.
- When you want to sum up all cells in a range where cells in another range meet a set criteria
When constructing the SUMIF function, it is vital that you understand how to specify the criteria. Any mistake in the criteria will simply make it impossible for you to get the sum if the cells are equal to your specified value.
Figure 1. Example 1: Sum if cells are equal to “Christian”
In the above worksheet, the range will be B2:B11. This is where we have the various variables among which we shall specify our criteria. The range shows religion of the various individuals in column A. Our criteria, in this case, will be “Christian.” The next component is the range-value, which is specified as C2:C11. Here is where we have the various values where we shall base our criteria.
For us to get the sum if cells in column C corresponding to column B is equal to Christian, we specify our function as below:
The function will give the total of the cells in column B if they are equal to “Christian”. It is important to note the quotation marks and how they have been used. If you leave them out or use them in a different way, then you will get an error.
A quick run of the formula using the data in the above worksheet will look as below:
Figure 2. Result
The formula will sum up all the values that correspond to Christian, and give us the result.