As you work with data in Excel, you may find yourself needing to find the sum of values meeting a certain condition. Of course, the option always exists to sort through and pick out values manually. But there’s a much easier way, called the SUMIF function. Let’s learn how to use SUMIF in Excel.
How to Use SUMIF in Microsoft Excel
Imagine a few common scenarios as you work through data in Excel. For example, you have a column of many numbers. You want to sum only the values that are greater than, or equal to, 25.
Or, you might have a list of sales data broken up by state. You want to sum up the total sales made in the state of Florida. In either case, simply using AutoSum or a basic formula will return the sum of all the values, not just those that you want.
Any time that you need to sum up values based on a single criteria, you can turn to Excel’s built-in SUMIF function. It’s actually quite simple to use, and brings a lot of power to your calculations. Let’s take a look at how to use it for both of the above examples.
SUMIF works with a variety of recognized math symbols, with = (equal to), > (greater than), and < (less than) all commonly seen in SUMIF functions. Oftentimes, you’ll have to combine multiple operators like these together to get the data results you want.
Let’s perform the first practice example, summing values within a given range that are greater than or equal to 25. Begin by clicking into any blank cell on your worksheet. Type in an = sign first. This tells Excel that you’ll be inputting a formula into the selected cell.
Then, input your SUMIF function. Your formula now reads:
Now, it’s time to select your range. In this case, the range is cells A1:A8. The easiest way to make the selection is to click and drag your cursor over the cells, highlighting them. Excel will automatically reference them in your formula, which is now:
Then, type a comma. Now, Excel asks for your criteria. Remember, it’s numbers that are greater than or equal to 25 in this example. Continue your formula with “>=25”, ensuring that you place the double quotation marks where shown.
Your formula is:
Finally, you’ll need the SUM_RANGE. Again, this is A1:A8. Click and drag again, then close your parentheses by typing ). Your finished formula is:
Hit Enter on your keyboard, and Excel returns your solution of 240. This is the sum of every number equal to or greater than 25 in your dataset.
Now, let’s look at our other example. Here, you want to sum up the total sales for Florida. Begin by placing the SUMIF function into an empty cell, as before:
Here, things change. Keep in mind, your condition is text-based and thus the range will be the column of state names. Click and drag to select them, then input your condition:
The SUM_RANGE, of course, will be column F, the numerical values. Click and drag to select this range, then close your parentheses, completing your formula as:
Hit Enter once again, and Excel returns your sum, $8,520, for total sales in Florida.
As you can see, the SUMIF function in Microsoft Excel is an easy way to filter data that you’re summing up. It helps you quickly process large volumes of information, pulling out and working with only those values that matter most to you. Give it a try next time you need to add numbers in Excel.