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.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!

Office Online
OneDrive with 1TB
24/7 support

What is the SUMIF function used for?

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 worksheets. For example, if you edit the descriptions of certain objects, they’ll no longer be included in the sum. You can also use interactive elements to developuseful forms with this function.

Fact

SUMIF combines the SUM and IF functions. You can achieve the same results by merging these two functions into one formula. SUMIF in Excel makes your work easier.

Syntax of the SUMIF function

The syntax of SUMIF isn’t very complex.

=SUMIF(Range;Searchcriteria;[Sum_Range])

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 references. You can also specify the values that you ultimately want to sum.
  • Criteria: You can define different types of search criteria. You can specify numbers, expressions, references, texts or other functions. If you want to use text or mathematical formulas, you have to enclose the criteria in double quotation marks. You can use wildcards ? (matches any single character) and * (matches any sequence of characters).
  • 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 distribution of cells as the Range argument. Each cell in the first argument is assigned one cell in the third argument. If you create a discrepancy, for example by specifying ten cells in Range but only five in Sum_range, Excel will automatically add five more cells to the third argument.

Fact

SUMIF functions similarly to the COUNTIF function.

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 difference is that you can specify multiple criteria and select more ranges. In addition, the order of the arguments is slightly different:

=SUMIF(Sum_Range;Range1;Searchcriteria1;[Area2;Searchcriteria2];…)

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.

SUMIF explained with examples

The Excel SUMIF function has a wide range of applications. The many possibilities 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.

Simple mathematical criterion

The simplest form of the SUMIF function uses logical operators. You can use the standard comparison operators in Excel:

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

 

=SUMIF(A2:A10;">=1000")

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

References to other cells

SUMIF becomes even more useful when you add further cell references. 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 transferred how much money. You can use the SUMIF function to sum only those amounts assigned to a certain name.

=SUMIF(A2:A15;"Adam";B2:B15)

In this case, the function adds up all the money transferred by Adam. Similarly, you can adjust the formula for all names in order to create a quick list.

Incidentally, 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.

=SUMIF(A2:A15;$D$2;B2:B15)

Date and SUMIF

Another useful feature of SUMIF is that it can workwith 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.

=SUMIF(B2:B16;"<"&EDATE(TODAY();-12);C2:C16)

The SUMIF function determines which cells in the range from B2 to B16 contain a date that is less than today’s date minus twelve months. The function sums only those cells in the range from C2 to C16 to which this condition applies.

Note

You have to enclose the comparison operator in double quotation marks. Use an ampersand (&) to link the comparison to the subsequent formula.

Addition with logical expressions

Suppose you need greater flexibility 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 checkboxes, for example. Checkboxes 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.

=SUMIF(C2:C16;TRUE;B2:B16)

You can keep the returned logical values visible or hide them, either by hiding the cells or simply by setting the font to white. Now you can freely select the amounts that you want to include in the sum.

More criteria with SUMIFS

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 worksheets 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 particular city.

=SUMIF(C2:C20;A2:A20;$F$2;B2:B20;$H$2)

Row C contains the values you want to sum. You search rows A and B for the criteria that you define in F2 and H2. Only if both criteria are true will the amount be included in the sum.

Tip

Euro signs (€) preceding column and row references instruct Excel to treat them as absolute references. When copying the formula, Excel adjusts normal cell references relative to the new position, whereas absolute references remain fixed.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!

Office Online
OneDrive with 1TB
24/7 support

Wait! We’ve got something for you!
Have a look at our great prices for different domain extensions.


Enter the web address of your choice in the search bar to check its availability.
.org
$1/1st year
then $20/year
.com
$1/1st year
then $15/year
.info
$1/1st year
then $20/year
.me
$1/1st year
then $20/year