SUMIF is one of the most helpful built-in Excel functions for users who want to add up numbers using specific criteria. It is especially useful when you only need to sum figures in your dataset that are less than, greater than, or equal to a particular number. However, since the SUMIF function requires the use of a specific formula, many Excel users find it confusing to implement correctly.
Fortunately, it is not as complicated as it seems. It might take a little practice, but using SUMIF is something that is definitely worth learning, particularly if you use large sets of data in Excel for work or school. And once you get the basic concept and formula down, you can start using SUMIF as a worksheet function in all of your Excel spreadsheets!
So, what does SUMIF stand for? What is the correct formula to use? What are some different ways that SUMIF can be used to improve your spreadsheets? Finally, if you are having trouble using SUMIF, where can you get professional help with Excel?
In today’s guide, we will answer all of these questions and more, but first, let’s look at what the SUMIF function is and how it works:
What Is SUMIF?
Many Excel users are immediately thrown off by the name of this function. However, “SUMIF” is actually an apt description of how the function works. SUMIF is literally a combination of the SUM and IF functions in Excel. If you’re familiar with these, their names are also pretty self-explanatory.
The SUM function adds up the sum total of multiple values. For example, if you want to add up the total value of a specific row or column, Excel has a handy AutoSum feature that does all the math for you with the click of a button. The limitation of the SUM feature is that it can only add up the sums in a selection of cells. In other words, you cannot add criteria to let Excel know which figures to include or exclude from the formula when using AutoSum alone.
The IF function is a bit broader, as it allows you to test different criteria against an expected result. In essence, this function lets you make an “if/then” statement within Excel. Let’s say you want to know if values in a column meet specific criteria. You can set the IF function to return a “Yes” for every cell that meets the criteria and a “No” for every cell that does not. This can be very useful, as it works with both numerical and non-numerical datasets. For example, if you want to see which cells equal 1, you could use this formula:
=IF(B2=”1”, Yes, No)
This formula tells Excel that you are looking at column B2. Then, for every cell in the B2 column, you want it to return a “Yes” answer if it equals 1 and a “No” answer if it does not equal 1. You could even change the result criteria to answers other than “Yes” and “No.”
So, what does all of this have to do with SUMIF? As previously mentioned, SUMIF combines these two functions. Rather than merely adding up all the numbers in a given section or getting the results of an “if/then” question, SUMIF enables you to add up the values of cells within a section based on certain criteria. If you have relevant data in multiple tables, you can also use SUMIF with VLOOKUP functions together to find and implement the values you need.
But what does this really mean? Let’s look at a SUMIF example to better understand how it works:
| A | B | C | D | E |
| 1 | Original Stock Value | Date of Purchase | Criteria | SUMIF |
| 2 | $1000 | 10/21/2001 | >1000 | $5000 |
| 3 | $2000 | 06/11/2011 | ||
| 4 | $1000 | 07/03/2005 | ||
| 5 | $3000 | 09/10/2003 |
In the table above, you’ll see a relatively simple breakdown of stock values and their respective purchase dates. But what if you want to add up the current values of stocks that are valued above $1000? To do this, you’ll need to use the SUMIF function.
In this case, you must set a range and criteria. The range would be A2:A5, as this range includes all of the relevant cells within the column for stock values. Then, you’ll need to set the criteria (visible in cell C2), which includes all values above $1000. When done using the correct formula, this will give you the sum value based on your criteria:
$2000 + $3000 = $5000
How To Use the SUMIF Function in Excel
Now that you understand the basic concepts that make up the SUMIF function, let’s take a look at how to use it in Excel. The SUMIF formula is as follows:
=SUMIF(Range, “Criteria”, [Sum_Range])
As you can see, there is an extra value that we did not discuss in the previous section. The “Sum_Range” is optional and it only applies if you want to add values in one row or column based on corresponding values in a different row or column. Let’s look at another example spreadsheet to see how it works:
| A | B | C | D | E | F |
| 1 | Original Stock Value | Date of Purchase | Current Stock Value | Criteria | SUMIF |
| 2 | $1000 | 10/21/2001 | $2500 | >1000 | $6500 |
| 3 | $2000 | 06/11/2011 | $3000 | ||
| 4 | $1000 | 07/03/2005 | $1300 | ||
| 5 | $3000 | 09/10/2003 | $3500 |
In this table, we’ve added an additional column for “Current Stock Value.” This will allow you to input the optional Sum_Range attribute to add the values of one section based on the criteria set in another. So, what if you want to sum the current stock values that were bought at a value higher than $1000? You can quickly find your answer by using this SUMIF formula:
=SUMIF(A2:A5,D2,C2:C5)
Now, here is the math that Excel will do to find the resulting value:
$3000 + $3500 = $6500
If this is your first time inputting a SUMIF formula in Excel, remember that you can pick any empty cell to input the formula and then hit “Enter” to get the result.
If this still feels confusing, let’s recap what each of these elements actually means:
- SUMIF: This is just telling Excel that you want to use the SUMIF function.
- Range: The Range is the section you want to evaluate using specific criteria. In the example above, the Range includes all of the values from A2 to A5. If a Sum Range is not included, the Range acts as both the range to be evaluated by the criteria and the range of cells summed up if they match the criteria.
- Criteria: This section is where you tell Excel how you want it to pick cells to add together. In this case, we’ve set a criterion that is greater than 1000.
- Sum Range: Again, this section is only necessary if you are using multiple columns to determine which values will be added together. In this case, the values that will determine the resulting sum are located in cells C2 to C5.
SUMIF vs SUMIFS
What should you do if you need to use a SUMIF with multiple criteria? Fortunately, you can quickly do this by using SUMIFS. With SUMIFS, you use the same basic concepts involved in the SUMIF function (range, criteria, and sum range), but you add one or more additional criteria. Here is how you can format the SUMIFS formula:
=SUMIFS(Sum_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], …)
As you can see, this allows you to add as many criteria as needed. Though you probably won’t need more than a few criteria to handle most tasks with SUMIFS, you can enter up to 127 Criteria/Criteria Range combinations in Excel. Naturally, advanced SUMIFS functions in Excel can get quite complicated if you’re using a lot of criteria, so be sure to input your formulas correctly!
Need help from dependable software experts? Reach out to Geeker today for on-demand IT and software solutions!