How to Use SUMIF in Microsoft Excel

With the help of Microsoft Excel's SUMIF function, you may determine the total values of several cells depending on a set of conditions. This article will walk you through each step using the SUMIF formula.

If you use Excel for data analysis, you may have already aggregated your data using the SUM function. The term sum simply means total, but if you're working with a lot of data, you'd likely like to get a selected result rather than the complete total from a range of cells.

What does the SUMIF function do, and how does it work?

As we've already established, the SUM function in Microsoft Excel is often used to determine a range of cells' sum (total). However, SUM is a fundamental function that doesn't let the user choose which cells to include in the calculation; instead, it includes every cell.

As a result, SUM is not as helpful for sophisticated data processing. Instead, you may use SUMIF to get around this. By using SUMIF, you may instruct Excel to utilise a logical statement—in this example, an IF statement—to assist it in deciding which cells to consider before calculating the total value.

We'll offer you an example. You might use the SUMIF function to find the total amount of sponsorships if the students are members of, say, the red team. If you have a list of students collecting money and want to determine the total depending on what "team" they belong to (say, red or blue), use SUMIF.

Due to the constraints of an IF statement, you can only have one criterion (for example, cell range includes "male") if you use the SUMIF function. Use an alternate function, such as SUMIFS, to combine the IF and AND operators in a single expression if your logical statement requires more criteria.

Things to think about before utilising Excel's SUMIF function

Although there aren't many restrictions on the SUMIF function, you still need to be aware of them. These consist of:

• Without a sum range parameter, the SUMIF computation will additionally employ the cell range referred to in the first range argument.
• You may utilise information from other cells as part of a SUMIF criterion argument by using the ampersand (&) symbol in a SUMIF formula. For instance, the formula =SUMIF(A2:D1, &A9) would seek cells in the A2 to D1 range whose values are lower than those of cell A9.
• To allow for more sophisticated criteria, concatenation with an ampersand may also be used with other functions, such as the DATE function.
• When searching for partial matches when your criterion is a text string, you may use wildcards (such as? to search for characters by length or * to search for characters in sequence). Put a tilde () sign in front of these special characters if you need to search for them rather than include them in your criteria.
• The criterion argument's text strings are limited to 255 characters in length. A #VALUE! error message will be returned for longer strings.

An explanation of how to utilise SUMIF in an Excel calculation.

These procedures must be followed if you wish to utilise the SUMIF function in an Excel worksheet.

1. Pick an Empty Cell

Open an Excel spreadsheet and choose an empty cell as your starting point. Press the ribbon bar's formula bar while the cell is chosen to bring it into focus.

You may start writing your SUMIF formula while the flashing cursor is active in the formula bar.

2. Establish the Starting Cell Range

You must start writing the SUMIF formula while the formula bar is active, using the above-described syntax and structure. Type =SUMIF(in the ribbon bar to get started.

The first cell range will then need to be chosen. This will include the information that the SUMIF function will compute to find the total if you do not use the sum range criterion at the end of your calculation. Otherwise, just your criteria will be tested using this data.

Use the start and end cells in the range to input your cell range, or pick the cells using your mouse or touchpad. For instance, =SUMIF(A2:A9) will employ the A2–A9 cell range.

3. Establish the SUMIF standards.

After choosing the starting range, you must decide what the SUMIF requirements are. The formula will employ this logical IF operator test to determine which cells will be counted and which ones will be disregarded.

You may include integers, text strings, mathematical operations, wildcards, and other cell references as part of your criteria.

Examples of various SUMIF requirements include:

• When using the less-than or greater-than operators, enter =SUMIF(B2:B9, "25").
• A text string that matches: A2:A9, "L*," B2:B9, =SUMIF
• =SUMIF(A2:A9, "to*") uses a wildcard to match a portion of a string.
• Finding cells with a specified character length using a wildcard depending on the number of question marks used A2:A9, "?"; B2:B9; =SUMIF
• =SUMIF(A2:A10,"",B2:B10) for all cells in the range that are empty.
• Cells that don't meet the requirements are =SUMIF(A2:A9,">Toby",B2:B9).
• =SUMIF(A2:A10,">",B2:B10) for all cells in the range that are not empty.
• Dates are appropriately used by concatenating them in the following ways: =SUMIF(C2:C9, ">"&DATE(2020,12,1),B2:B9). Concatenation is used to add data from additional cells: =SUMIF(B2:B9,""&B13).
• These are but a few samples that you may modify to fit your requirements. You must conclude the formula with a closing parenthesis to see the output result if you want to use your beginning cell range as a part of your calculation, for example, =SUMIF(A2:A9, "25">).

4. Decide on your Sum Range Criterion

A SUMIF formula's optional sum range final parameter may be utilised to construct more intricate logical checks. You must include your sum range criterion before finishing the calculation if you wish to test one cell range before computing the SUM of a second.

The first and final empty cells in your range must be specified, just as with the initial range requirements, before closing the formula with a closing parenthesis.

For instance, the expression =SUMIF(A2:A9, "L*", B2:B9) will check the cells between A2 and A9 to see whether any text strings containing the letter L are present. If they do, SUM will be used to determine the matching numbers in cells B2 through B9.

Excel will display a #VALUE! or a similar error message if your cell ranges or criteria are wrong.

Last thoughts

Data may be swiftly extrapolated from an Excel spreadsheet using tools like SUMIF. SUMIF should be helpful for more straightforward data sets, but options like SUMIFS and pivot tables may be able to assist you with more complicated analysis. It's also one of numerous Excel formulae that data analysts are advised to utilise often, along with the AVERAGE function, XLOOKUP, and COUNTIFS, to mention a few.