Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel SUMIF function

In Excel, the SUMIF function can help to sum cells based on one criterion, this article will explain this function's syntax and introduce some examples for using the SUMIF function.


 Syntax:

The syntax for the SUMIF function in Excel is:

=SUMIF (range, criteria, [sum_range])

 Arguments:

  • range: Required. The range of cells that you want to apply the criteria.
  • criteria: Required. The criteria used to determine which cells need to add. The criteria can be text, number, date, logical expression, cell reference or another function.
  • Note: Any text criteria or criteria containing mathematical symbols must be enclosed in double quotations marks. If the criteria is numeric, double quotation marks are not required.
  • sum_range:Optional. It is the range of cells to sum together. If this parameter is omitted, it uses range as the sum_range.

 Return:

Return the summation for a list of numbers based on the specific criteria.


 Examples:

Example 1: Sum if values are greater than, less than, equal to or other comparison operators

In Excel, you can use the SUMIF function to add up values greater than, less than, equal to or not equal to a given criteria.

Supposing, you have a table which contains Name, Qty and Price columns as below screenshot shown, how could you get the total price that the Qty is greater than 250?

Please apply the below formula into a blank cell:

=SUMIF(B2:B8,">250",C2:C8)
  • Tips: In the above formula:
  • B2:B8: is the range of cells where the criteria will be looking for.
  • ">250": is the condition you want to sum the cell values based on.
  • C2:C8: refers to the range to be summed.

Then press Enter key to get the total price that the Qty is greater than 250 as following screenshot shown:

Note: You can use other comparison operators, such as greater than or equal to (>=), less than (<), less than or equal to (<=), equal to (=), not equal to (<>) to your need.


Example 2: SUMIF formulas with text criteria

If you want to sum cell values based on whether a specific text value exists in another column, the SUMIF function also can do you a favor.


Case 1: Sum cell values if contains specific text in another column

Take the below data as example, to get all the total Qty if the name is David in column A.

Please enter or copy the below formula into a blank cell:

=SUMIF(A2:A8, "David", B2:B8)
  • Tips: In the above formula:
  • A2:A8: is the range of cells where the criteria will be looking for.
  • "David": is the condition you want to sum the cell values based on.
  • B2:B8: is the range of cells to be summed.

Note: You can also use the cell reference to replace the condition in the above formula as this: =SUMIF(A2:A8, D2, B2:B8).


Case 2: Sum cell values not equal to a specific value

If you want to sum cells when the cells are not equal to a specific value, for example, to sum all Qty of the people except David, how could you do?

Please apply the below formula into a blank cell:

=SUMIF(A2:A8, "<>David", B2:B8)
  • Tips: In the above formula:
  • A2:A8: is the range of cells where the criteria will be looking for.
  • "<>David": is the condition you want to sum the cell values based on.
  • B2:B8: is the range of cells to be summed.

Note: You can also use the cell reference to replace the condition in the above formula as this: =SUMIF(A2:A8,"<>"&D2, B2:B8), you must use the double quotes ("") to enclose the comparison operators and use ampersand (&) to concatenate the cell reference. See screenshot:


Example 3: SUMIF formulas with wildcard characters

Sometimes, you may need to sum the cell values based on partial text match instead of the exact match. In this case, you can combine the wildcard characters asterisk (*) or question mark (?) with the SUMIF function.

Asterisk (*): Represents any number of characters

Question mark (?): Represents anyone single character in a specific position


Case 1: Sum values based on partial match with asterisk (*) character

For instance, I have a table as following screenshot shown, now, I want to sum the total orders of the product which text contains Kutools.

Here, please apply the following formula into a blank cell:

=SUMIF(A2:A8, "*Kutools*", B2:B8)

Then, press Enter key to get the below result:

Notes:

1. If you want to sum cell values that the start with Kutools, please use this formula: =SUMIF(A2:A8, "Kutools*", B2:B8).

2. To sum cell values if the corresponding cell in Column A ends with the text Kutools, please use this formula: =SUMIF(A2:A8, "*Kutools", B2:B8).

3. You can also use the cell reference to replace the condition in the above formula as this: =SUMIF(A2:A8, "*"&D2&"*", B2:B8). You should use the double quotes ("") to enclose the asterisk and use ampersand (&) to concatenate the cell reference. See screenshot:


Case 2: Sum values with specific number of characters with question mark (?)

If you want to sum the cell values in a list which corresponding cell has exactly 5 characters long as following screenshot shown. How could you solve this task in Excel?

To deal with this job, you should apply the below formula:

=SUMIF(A2:A8, "?????", B2:B8)

Tip: In this formula: one question mark represents one single character, five-question marks indicate 5 characters long.

And then, press Enter key, you will get the result you need:


Example 4: Sum cell values that corresponding to blank or non-blank cells

In Excel, the SUMIF function also can help to sum values in a list that the adjacent cell is blank cell or non-blank cells.


Case 1: Sum cell values if adjacent cell is blank

Please enter or copy any one of the below formulas into a cell where you want to get the result:

=SUMIF(A2:A8,"=",B2:B8)
=SUMIF(A2:A8,"",B2:B8)

Then, press Enter key to get the result of the total price if there are blank cells in Column A, see screenshot:


Case 2: Sum cell values if adjacent cell is non-blank cell

If you need to sum values where the adjacent cells contain texts excluding blank cells, please apply the below formula:

=SUMIF(A2:A8,"<>",B2:B8)

And then, press Enter key, all values where the adjacent cells contain values have been summed together, see screenshot:


Example 5: SUMIF with dates in Excel

Normally, you can also use the SUMIF function to sum values based on specific date in Excel.


Case 1: Sum values if date is current date or less than, greater than today

To sum the cell values which date is current date, you can use the below formula:

=SUMIF(B2:B8, TODAY(), C2:C8)

Then, press Enter key, all the cells that the corresponding date is today are summed. See screenshot:

Notes:

1. To sum the values if the corresponding date is less than or before today, please use this formula: =SUMIF(B2:B8, "<"&TODAY(), C2:C8).

2. To sum the values if corresponding date is greater than or after today, please use this formula: =SUMIF(B2:B8, ">"&TODAY(), C2:C8).


Case 2: Sum values between two given dates

If you want to sum the values if the date between two given dates, you can combine two SUMIF functions together for solving this job.

Please apply the below formula:

=SUMIF(B2:B8, ">=6/15/2019", C2:C8) - SUMIF(B2:B8, ">=7/31/2019", C2:C8)

And then, press Enter key, the total value between two given dates has been calculated, see screenshot:

Note: You can also use the cell reference to replace the condition in the above formula as this: =SUMIF(B2:B8, ">="&E2, C2:C8) - SUMIF(B2:B8, ">="&F2, C2:C8), see screenshot:


Example 6: Sum values in multiple columns in Excel

This SUMIF function also can help you to sum values in multiple columns based on one condition. For example, if you have a range of data as below screenshot shown, now, to get the total values of the product KTE in four months - Jan, Feb, Mar and Apr. How could you do?

Normally, the SUMIF function is used to sum a column of values based on one criterion, to sum values in multiple columns, you can combine multiple SUMIF functions for solving this task.

Please apply the below formula into a blank cell:

=SUM(SUMIF(A2:A8,G2,B2:B8), SUMIF(A2:A8,G2,C2:C8), SUMIF(A2:A8,G2,D2:D8),SUMIF(A2:A8,G2,E2:E8))

Then press Enter key to get the correct result you need, see screenshot:

Note: The above SUMIF function is somewhat difficult for you to use if there are a lot of columns need to sum, so here, a simple SUMPRODUCT function can help you deal with this job as well. Please use this formula: =SUMPRODUCT((A2:A8=G2)*(B2:E8)), and then press Enter key to get the result:

  • Tips: In the above formula:
  • A2:A8: is the range of cells where the criteria will be looking for.
  • G2: is the condition you want to sum the cell values based on.
  • B2:E8: is the range of cells to be summed.


 More SUMIF function articles:

  • Sum Corresponding Values With Same Date in Excel
  • Have you ever tried to sum corresponding values based on the same date from another column in Excel? Supposing, I have the following data range (A1:B13), column A is the date range contains some duplicate dates, and column B has the number of order, now, I need to add the numbers together based on the same date in column A.How to finish it quickly and easily in Excel?
  • Sumif Date Is Less/Greater Than Today In Excel
  • Supposing we have a sale volume table with planning and actual volumes of every day. Now we are required to count occurred dates before today and calculate the total planning volumes before today in order to dynamically compare the planning volumes and actual volumes. Here I will apply the SUMIF function to get it done in Excel quickly.
  • Sumif With One Or More Criteria In Excel
  • In Excel, sum values based on one or more criteria is a common task for most of us, the SUMIF function can help us to quickly sum the values based on one condition and the SUMIFS function help us to sum values with multiple criteria. This article, I will describe how to sum with one or more criteria in Excel?
  • Sumif Based On Partial Match In Excel
  • Take the below data as an example, I want to sum cell values in column B when there contains partial “KTE” text in column A. How to achieve it? This article, I will talk about how to sum cell values based on a partial match in Excel worksheet.

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Screen Shot of Excel (with Office Tab installed)

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.