Adding numbers together in Microsoft Excel is a basic calculation that can use the SUM function. What if you want to add those values but only if they meet certain conditions? This is when the SUMIF function comes in.
With SUMIF, you can add the values in the cells you specify as long as they meet specific criteria. Maybe you want to find the total sales but only for certain products or the total revenue but only for particular locations.
If your Excel sheet is set up in a way that your calculation isn’t easily determined, the SUMIF function and its formula can help.
Use SUMIF For a Single Cell Range
The syntax for the function is
SUMIF(cell_range, criteria, sum_range) where the first two arguments are required. Because
sum_range is optional, you can add numbers in one range that correlate to criteria in another.
To get the basic feel of the function and its arguments, let’s start by using a single range of cells without the optional argument.ADVERTISEMENT
You could add values in a cell range only if they are greater than a certain amount. Enter the following formula, replacing the cell references and criteria with your own.
This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000.
On the flip side, you can add numbers that are less than a certain amount using this formula:
This adds the numbers in cells B2 through B7 only if they are less than 10,000.
For one more example, you can add numbers that are the same amount with this formula:
This adds the numbers in cells A2 through A7 only if they are exactly 5,000.
Now let’s put that conditional argument to work,
sum_range. Here we’re calculating expenses and revenue. With SUMIF, we can calculate the revenue for locations whose expenses meet our criteria and vice versa.