The Excel SUMIF function explained

Excel makes it easy to add up values in your analyses and reports. The standard SUM function is perfectly adequate in most cases. But you often want to add only specific numbers instead of adding up all the values. Excel provides the SUMIF function for this purpose.

The SUMIF function in Excel combines SUM with a logical operation. The function only sums values if they meet the criteria you specify. The criteria you specify can refer to the value itself or to a cell that refers to the value. For example, you can sum only numbers that are less than 1000. Or you can sum only the values in the “Office Supplies” category.

You can use this function to build formulas that respond to changes in your work­sheets. For example, if you edit the de­scrip­tions of certain objects, they’ll no longer be included in the sum. You can also use in­ter­act­ive elements to develop useful forms with this function.

The syntax of SUMIF isn’t very complex.

This function has three arguments. The last one is optional:

• Range: Specify the range of cells to which you want to apply the criteria. The cells contain numbers or values. You can specify monetary values and dates. The cells can contain names, arrays or ref­er­ences. You can also specify the values that you ul­ti­mately want to sum.
• Criteria: You can define different types of search criteria. You can specify numbers, ex­pres­sions, ref­er­ences, texts or other functions. If you want to use text or math­em­at­ic­al formulas, you have to enclose the criteria in double quotation marks. You can use wildcards ? (matches any single character) and * (matches any sequence of char­ac­ters).
• Sum_range: If the values you want to sum are not already included in the first argument, you can specify them here.

If you use the optional Sum_range argument, it should contain the same number and dis­tri­bu­tion of cells as the Range argument. Each cell in the first argument is assigned one cell in the third argument. If you create a dis­crep­ancy, for example by spe­cify­ing ten cells in Range but only five in Sum_range, Excel will auto­mat­ic­ally add five more cells to the third argument.

You can use the advanced function SUMIFS in addition to SUMIF. This feature is available in Excel 2007 and higher and allows you to specify multiple criteria. The principle is similar to SUMIF: You define the range that you want to search, the criteria, and the range of cells you want to sum. The main dif­fer­ence is that you can specify multiple criteria and select more ranges. In addition, the order of the arguments is slightly different:

Whereas the sum_range argument is optional in SUMIF, the advanced function requires you to specify the cells you want to sum. You must specify at least one criteria, define the range and you can specify up to 127 search criteria.

The Excel SUMIF function has a wide range of ap­plic­a­tions. The many pos­sib­il­it­ies of this function are best explained using examples. You can use these examples as starting points for your own projects and adapt them to your needs.

The simplest form of the SUMIF function uses logical operators. You can use the standard com­par­is­on operators in Excel:

• less than: <
• greater than: >
• less than or equal to: <=
• greater than or equal to: >=
• equal to: =
• not equal to: <>

The formula adds all values in cells A2 to A10 that are greater than or equal to 1000.

SUMIF becomes even more useful when you add further cell ref­er­ences. For example, you can sum only those values that match a specific text in another cell.

Suppose you have an unordered list of multiple deposits from different people to a savings account. You now want to see who has already trans­ferred how much money. You can use the SUMIF function to sum only those amounts assigned to a certain name.

In­cid­ent­ally, you don’t have to enter the text in the formula itself. You can also create a cell reference that contains the names. It can also be very useful to put the names in a drop-down list. The function then reads the search criteria from the cell.

Another useful feature of SUMIF is that it can work with dates. Suppose you want to sum only those deposits that are over one year old. Once again, we’ll start with an unsorted list. You have to compare the deposit date with today’s date. To do this, use the EDATE and TODAY functions.

Suppose you need greater flex­ib­il­ity when summing values. You can also specify logical values as criteria for the SUMIF function. If the contents of the cell are TRUE, the amount in the cell is included in the sum. This can be useful if you use check­boxes, for example. Check­boxes return either TRUE (if checked) or FALSE (if unchecked) in the cell of your choice. This way your search criteria simply refers to these cells and searches for the TRUE statement in the cell.

Often you want to sum cells that meet multiple criteria. You can’t do this with SUMIF alone, so you have to use the SUMIFS function. It lets you search work­sheets for two or more criteria that must be met.

Let’s say you want to add up the profits of one of your sales reps, but only those from a par­tic­u­lar city.