by This tutorial explains the Excel SUMIF function in plain English. The main focus is on real-life formula examples with all kinds of criteria including text, numbers, dates, wildcards, blanks and non-blanks.
Microsoft Excel has a handful of functions to summarize large data sets for reports and analyses. One of the most useful functions that can help you make sense of an incomprehensible set of diverse data is SUMIF. Instead of adding up all numbers in a range, it lets you sum only those values that meet your criteria.
So, whenever your task requires conditional sum in Excel, the SUMIF function is what you need. A good thing is that the function is available in all versions, from Excel 2000 through Excel 365. Another great thing is that once you've learned SUMIF, it will take you very little effort to master other "IF" functions such as SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, etc.
SUMIF in Excel - syntax and basic uses
The SUMIF function, also known as Excel conditional sum, is used to add up cell values based on a certain condition.
The function is available in Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.
The syntax is as follows:
As you see, the SUMIF function has 3 arguments - first 2 are required and the last one is optional.
- Range (required) - the range of cells to be evaluated by criteria.
- Criteria (required) - the condition that must be met. It may be supplied in the form of a number, text, date, logical expression, a cell reference, or another Excel function. For example, you can enter the criteria such as "5", "cherries", "10/25/2014", "<5", etc.
- Sum_range (optional) - the range to sum if the condition is met. If omitted, then range is summed.
Note. Please pay attention that any text criteria or criteria containing logical operators must be enclosed in double quotation marks, e.g. "apples", ">10". Cell references should be used without the quotation marks, otherwise they would be treated as text strings.
Basic SUMIF formula
To better understand the SUMIF syntax, consider the following example. Suppose you have a list of products in column A, regions in column B, and sales amounts in column C. Your goal is to get a total of sales for a specific region, say North. To have it done, let's build an Excel SUMIF formula in its simplest form.
You start with defining the following arguments:
- Range - a list of regions (B2:B10).
- Criteria - "North" or a cell containing the region of interest (F1).
- Sum_range - the sales amounts to be added up (C2:C10).
Putting the arguments together, we get the following formula:
=SUMIF(B2:B10, "north", C2:C10)
or
=SUMIF(B2:B10, F1, C2:C10)
Both formulas only sum sales in the North region:
Note. The sum_range parameter actually defines only the upper leftmost cell of the range to be summed. The remaining area is defined by the dimensions of the range argument. In practice, this means that sum_range argument does not necessarily have to be of the same size as range argument, i. e. it may have a different number of rows and columns. However, the top left cell must always be the right one. For example, in the above formula, you can supply C2, or C2:C4, or even C2:C100 as the sum_range argument, and the result will still be correct. However, the best practice is to provide equally sized range and sum_range.
Note. The SUMIF function is case-insensitive by nature. However, it is possible to force it to recognize the text case. For full details, please see Case-sensitive SUMIF in Excel.
How to use SUMIF in Excel - formula examples
Hopefully, the above example has helped you gain some basic understanding of how the function works. Below you will find a few more formulas that demonstrate how to use SUMIF in Excel with various criteria.
SUMIF greater than or less than
To sum numbers greater than or less than a particular value, configure the SUMIF criteria with one of the following logical operators:
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
In the table below, supposing you wish to add up the sales numbers for the items that ship in 3 or more days. To express this condition, put a comparison operator (>) before the number and surround the construction in double quotes:
=SUMIF(C2:C10, ">3", B2:B10)
If the target number is in another cell, say F1, concatenate the logical operator and cell reference:
=SUMIF(C2:C10, ">"&F1, B2:B10)
In a similar manner, you can sum values smaller than a given number. For this, use the less than (<) operator:
=SUMIF(C2:C10, "<3", B2:B10)
SUM IF equal to
A SUMIF formula with the "equal to" criteria works for both numbers and text. In such criteria, the equals sign is not actually required.
For instance, to find a total of the items that ship in 3 days, either of the below formulas will do:
=SUMIF(C2:C10, 3, B2:B10)
or
=SUMIF(C2:C10, "=3", B2:B10)
To sum if equal to cell, supply a cell reference for criteria:
=SUMIF(C2:C10, F1, B2:B10)
Where B2:B10 are the amounts, C2:C10 is the shipment duration, and F1 is the desired delivery time.
Likewise, you can use the "equal to" criteria with text values. For instance, to add up the Apples amounts, choose any of the formulas below:
=SUMIF(A2:A10, "apples", B2:B10)
=SUMIF(A2:A10, "=apples", B2:B10)
=SUMIF(A2:A10, F1, B2:B10)
Where A2:A10 is the list of items to compare against the value in F1.
The above formulas imply that the criterion matches the entire cell contents. Consequently, the SUMIF function will add up Apples sales but not, say, Green Apples. To sum partial matches, construct the "if cell contains" criteria like in this SUMIF wildcard formula.
Note. Please pay attention that, in Excel SUMIF formulas, a comparison or equals operator should always be enclosed in double quotes, whether used on its own or together with a number or text.
SUM IF not equal to
To build the "not equal to" criteria, use the "<>" logical operator.
When a value, either text or number, is hardcoded in the criteria, remember to surround the entire construction with double quotes.
For example, to sum the amounts with shipment other than 3 days, the formula goes as follows:
=SUMIF(C2:C10, "<>3", B2:B10)
To find a total of all the items except Apples, the formula is:
=SUMIF(A2:A10, "<>apples", B2:B10)
When the criterion is in another cell, concatenate the "not equal to" operator and a cell reference like this:
=SUMIF(A2:A10, "<>"&F1, B2:B10)
SUM IF blank
This example shows how to sum cells in one column if a corresponding cell in another column is blank. There are two formulas to fulfill the task. Which one to use depends on your interpretation of a "blank cell".
If "blank" means cells that contain absolutely nothing (no formula, no zero-length string returned by some other function), then use "=" for criteria. For example:
=SUMIF(B2:B10, "=", C2:D10)
If "blank" includes empty strings (for example, cells with a formula like =""), then use "" for criteria:
=SUMIF(B2:B10, "", C2:D10)
Both formulas return a total of sales for undefined regions, i.e. where a cell in column B is blank:
SUM IF not blank
To make "if cell is not blank then sum" kind of formula, use "<>" as the criteria. This will add up all cells that contain anything in them, including zero-length strings.
For instance, here's how you can sum sales for all the regions, i.e. where column B is not blank:
=SUMIF(B2:B10, "<>", C2:D10)
Excel SUMIF with text criteria
When adding up numbers in one column based on text values in another column, it's important to differentiate between exact and partial match.
| Criteria | Formula Example | Description |
|---|---|---|
| Sum if equal to | Exact match: =SUMIF(A2:A8, "bananas", C2:C8) |
Sum values in cells C2:C8 if a cell in column A in the same row contains exactly the word "bananas" and no other words or characters. Cells containing "green bananas", "bananas green", or "bananas!" are not included. |
| Sum if cell contains | Partial match: =SUMIF(A2:A8, "*bananas*", C2:C8) |
Sum values in cells C2:C8 if a corresponding cell in column A contains the word "bananas", alone or in combination with any other words. Cells containing "green bananas", "bananas green", or "bananas!" are summed. |
| Sum if not equal to | Exact match: =SUMIF(A2:A8, "<>bananas", C2:C8) |
Sum values in cells C2:C8 if a cell in column A contains any value other than "bananas". If a cell contains "bananas" together with some other words or characters like "yellow bananas" or "bananas yellow", such cells are summed. |
| Sum if cell does not contain | Partial match: =SUMIF(A2:A8, "<>*bananas*", C2:C8) |
Sum values in cells C2:C8 if a cell in column A does not contain the word "bananas", alone or in combination with any other words. Cells containing "yellow bananas" or "bananas yellow" are not summed. |
For real-life formula examples, please check out Sum if equal to and Sum if not equal to.
In the next section, we'll take a closer look at SUMIF formulas with partial match.
SUMIF formulas with wildcard characters
To conditionally sum cells by partial match, include one of the following wildcard characters in your criteria:
- Question mark (?) to match any single character in a specific position.
- Asterisk (*) to match any number of characters.
Example 1. Sum values based on partial match
Suppose you wish to total sales for all northern regions, including North, North-East, and North-West. To have it done, put an asterisk right after the word "north":
=SUMIF(B2:B10, "north*", C2:D10)
An asterisk on both sides will also work:
=SUMIF(B2:B10, "*north*", C2:D10)
Alternatively, you can type the region of interest in a predefined cell (F1), and then concatenate a cell reference and a wildcard character enclosed in quotes:
=SUMIF(B2:B10, F1&"*", C2:D10)
Or
=SUMIF(B2:B10, "*"&F1&"*", C2:D10)
Example 2. Sum if cell contains * or ?
To match a literal question mark or asterisk, place a tilde (~) before the character, e.g. "~?" or "~*".
For example, to sum sales for the regions marked with *, use "*~*" for criteria. In this case, the first asterisk is a wildcard and the second one is a literal asterisk character:
=SUMIF(B2:B10, "*~*", C2:D10)
If the criteria (* in our case) is entered in a separate cell, then concatenate a tilde and the cell reference, like this:
=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)
Example 3. Sum if another cell contains text
If your dataset contains various data types and you only want to sum cells corresponding to text values, the following SUMIF formulas will come in handy.
To add up values in cells C2:C8 if a cell in column A contains any text character(s):
=SUMIF(A2:A8,"?*", C2:C8)
To sum values in C2:C8 if a cell in column A contains any text value, including zero length strings:
=SUMIF(A2:A8,"*", C2:C8)
Both of the above formulas ignore non-text values such as numbers, dates, errors, and Booleans.
How to use Excel SUMIF with dates
Using dates as SUMIF criteria is very much like using numbers. The most important thing is to supply a date in the format that Excel understands. If you are not sure which date format is supported and which is not, the DATE function can be a solution.
Assuming you are looking to sum sales for the items delivered before 10-Sep-2020, the criteria can be expressed in this way:
=SUMIF(C2:C10, "<9/10/2020", B2:B10)
or
=SUMIF(C2:C10, "<"&DATE(2020,9,10), B2:B10)
or
=SUMIF(C2:C10, "<"&F1, B2:B10)
Where F1 is the target date.
To sum cells based on today's date, include the TODAY function in your criteria. For example, that's how you calculate a total of sales with a delivery date prior to today:
=SUMIF(C2:C10, "<"&TODAY(), B2:B10)
To sum within a date range, you need to define a smaller and larger date separately. This can be done with the help of the SUMIFS function that supports multiple criteria.
For example, to sum values in column B if a date in column C is between two dates, this is the formula to use:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
Where B2:B10 is the sum range, C2:C10 is the list of dates to check, F1 is the start date and G1 is the end date.
More formula examples can be found in SUMIFS with date range as criteria.
How to do SUMIF from another sheet
To conditionally sum data from a different sheet, provide external references for the SUMIF arguments. The easiest way is to start typing a formula, switch to another worksheet and select ranges using the mouse. Excel will insert all the references automatically, without you having to worry about the correct syntax.
For instance, the below formula will add up values in C2:C10 on the Data sheet based on the criteria in B3 on Sheet1:
=SUMIF(Data!B2:B10, B3, Data!C2:C10)
How to correctly use cell references in SUMIF criteria
To create a flexible formula, you normally insert all variable parameters in predefined cells instead of "hardcoding" them. With Excel SUMIF, that might be a bit of a challenge.
In the simplest case when summing "if equal to", you simply use a cell reference for criteria. For example:
=SUMIF(C2:C10, F1, B2:B10)
But when a cell reference is used together with a logical operator, the criteria should be provided in the form of a string. So, you use the double quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example:
=SUMIF(C2:C10, ">"&F7, B2:B10)
Please note that the comparison operators are enclosed in quotation marks while the cell references are not.
Why is my SUMIF formula not working?
There could be several reasons why Excel SUMIF is not working for you. Sometimes, your formula does not return what you expect only because the data type in a cell or in some argument isn't suited for the SUMIF function. Below is a list of important things to check.
1. SUMIF supports only one condition
The syntax of the SUMIF function has room for only one condition. To sum with multiple criteria, either use the SUMIFS function (adds up cells that meet all the conditions) or build a SUMIF formula with multiple OR criteria (sums cells that meet any of the conditions).
2. Range and sum_range should be of the same size
For a SUMIF formula to work correctly, the range and sum_range argument should have the same dimensions, otherwise you may get misleading results. The point is that Microsoft Excel does not rely on the user's ability to provide matching ranges, and to avoid possible inconsistency issues, it determines the sum range automatically in this way:
Sum_range defines only the upper left cell of the range that will be summed, the remaining area is determined by the size and shape of the range argument.
Given the above, the below formula will actually sum cells in C2:C10 and not in C2:D10. Why? Because range consists of 1 column and 9 rows, and so does sum_range.
=SUMIF(B2:B10, "north", C2:D10)
In older Excel versions, unequally sized ranges can cause lots of problems. In modern Excel, complex SUMIF formulas where sum_range has less rows and/or columns than range are also capricious. That is why it's a good practice to always define the same number of rows and columns for these two arguments.
3. Range and sum_range should be ranges, not arrays
Though SUMIF can process an array constant in criteria like shown in this example, it does not support arrays in range and sum_range. These two arguments can only be cell ranges.
5. SUMIF criteria syntax
For criteria, the SUMIF function allows using different data types including text, numbers, dates, cell references, logical operators (>, <, =, <>), wildcard characters (?, *, ~) and other functions. The syntax of such criteria is quite specific.
If the criteria argument includes a text value, wildcard character or logical operator followed by text, number or date, enclose the whole criteria in quotation marks. For example:
=SUMIF(B2:B10, "north*", C2:D10)
=SUMIF(C2:D10, ">100")
=SUMIF(B2:B10, "<>north", C2:D10)
=SUMIF(C2:C10, "<=9/10/2020", B2:B10)
When a logical operator is followed by a cell reference or another function, the criteria should be provided in the form of a string. So, you use an ampersand (&) to concatenate a logical operator and a reference or function. For example:
=SUMIF(B2:B10, ">"&F1)
=SUMIF(C2:D10, "<="&TODAY(), B2:B10)
4. SUMIF from another workbook not working
As with many Excel functions, SUMIF can refer to other sheets and workbooks, provided they are currently open.
For example, this formula will work fine as long as Book1 is open:
=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9,"apples",[Book1.xlsx]Sheet1!$F$2:$F$9)
And it will stop working as soon as Book1 is closed. This happens because the referenced ranges in closed workbooks get de-referenced into arrays. And since arrays are not supported in the range and sum_range arguments, SUMIF throws a #VALUE! error.
6. SUMIF does not recognize text case
By design, SUMIF in Excel is not case-sensitive, meaning it treats uppercase and lowercase letters as the same characters. To make a case-sensitive SUMIF formula, use the SUMPRODUCT function together with EXACT.
That's how to use SUMIF in Excel. Hopefully, our formula examples have given you some good insights. As always, I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel SUMIF examples (.xlsx file)
346 comments
Hi there,
I have a question on sumif and sumifs function. I have a list of data showing contracts names that took place over 2012-2014 for a specific client. I want to set up a summary tab where there are drop downs for contract name and years. so if I were to select 2014 and it'll give me a total figure of how much was made in that year. If I were to select specific contract name for 2014, it'll give me how much was made on that contract in 2014. Also if I was to select all years and all contracts, it'll give me an overall total.
Would anyone happen to know how I would go about doing that please??
thanks
Hi,
Is it possible to add a mathematical condition in Sumif formula. Please refer below example
A
1
2
3
4
5
6
7
Can I add a condition in Sumif such that add only those numbers which are divisible by 3.
One option is to insert a new column to calculate which values are divisible by 3 and use it as the criteria range for the SUMIFS formula.
=MOD(cell,3) equal to zero means that the cell value is divisible by 3.
Use the new column as the criteria range and 0 as the criteria in the SUMIFS formula to add the cells that are divisible by 3. Hope this helps!
Hi,
I am trying to autosum a total column in an invoice table spreadsheet in which it includes the data and the price visited per site. I have written a formula in the date column like such: =IF([@Date]"",167, 0). Hence if there is a date entered it will auto populate 167 in the Price column in the table, if it does not have a date inserted it just '$ - ". But when I try to SUM or IFSUM (=SUMIF([Date], "", [Price]) formulas it rather leaves a "$ - "or has the #### error. I have tried SUMPRODUCT, SUM, SUMIF, and SUMIFS formulas but cannot get it to total sum the columns that have a price compared to not having anything. Please help!
I am trying to use the sumif function to sum a series of job #'s that are specific to salespeople. I am exporting this list out of our accounting software & I noticed that the job #'s are actually labeled as "text" in my worksheet. What would my criteria be for the following:
Job Billed to Date
2074-14 4,306.11
2999-17 0.00
4000-17 0.00
4001-17 0.00
4002-17 0.00
4003-17 0.00
4004-17 0.00
4005-17 0.00
4006-17 0.00
4070-16 18,462.00
4076-16 10,133.00
4082-16 7,940.00
4083-16 7,610.00
4091-16 7,895.00
4092-16 5,925.00
4093-16 7,510.00
4094-16 0.00
4095-16 15,863.00
4096-16 7,876.00
4097-16 7,200.00
4098-16 18,360.00
4101-16 5,450.00
4102-16 7,500.00
4103-16 12,100.00
4104-16 0.00
4105-15 9,510.00
4105-16 0.00
4106-16 2,950.00
4107-16 7,754.00
4108-16 0.00
4999-17 0.00
5000-16 53,529.00
5004-16 22,683.00
5005-16 1,419,164.00
5007-16 30,299.00
5008-16 0.00
5011-16 0.00
5013-16 0.00
5014-16 0.00
5015-15 111,403.52
5015-16 0.00
5016-16 0.00
5017-16 0.00
5018-16 0.00
5024-15 57,166.00
5999-17 0.00
Column "A" is the job#, column "B" is the billed amount. I am trying to sum the sales for column "A", which each range represents a salesperson. Ex., series 2000-2999 jobs = Bob, series 4000-4999 = Joe & series 5000-5999 = Marie.
Here is the formula that I am trying to use, but it is not working. Any ideas? Thanks
=SUMIF('Sep 16'!A2:A1048576,">2000,<2999-99",'Sep 16'!B2:B1048576)
You could do:
=SUMIF('Sep 16'!A2:A1048576,"2*",'Sep 16'!B2:B1048576) to sum up all job# starting with 2 for instance (repeat accordingly for 3, 4 and 5s or build up a formula like so: C2&"*" for your criteria)
hi, just suppose we have 2 list. in first we have customers account and pending amount. in second list of some customers with account who have paid partial of full amount. then how to find net amount? if list are large and we cant sort them..
Great article! I thought I knew SUMIF but I learnt a lot of things there.
Similarly to sum-range indicating only the upperleft-most cell, it would seem that range actually indicates only the upperright-most cell is that correct ?
I stumbled upon a spreadsheet that I couldn't understand how, was giving the correct result, basically instead of =SUMIF($B$2:$B$10,$H$2:$H$10,$D$2:$D:$10) it read =SUMIF($A$2:$B$10,$H$2:$H$10,$C$2:$C:$10)
I now understand the shift to column C in 'sum_range' as 'range' was 2 columns wide, but I wanted to confirm the behavior of 'range'.
Thanks!
how to sum up c1:c50 when b1:b50 is not o or c or oc?
Hi, Great blog and I'm hoping you can help?
I have a large sheet called 'Errors' (11 columns) and in Column A I have employee names who have recorded till errors over 2016, so their names appear multiple times down the list and the value of the error short/over appears in Column J.
In a separate tab I have the full list of employee names, so for example A1 is Adam. In B1 I'm trying to create a formula that will return all shortages created by Adam in 2016 by looking at the other sheet (A1:K4000. I've been trying something like =VLOOKUP(A1,ErrorsA1:K4000,10,"<0) but it returns a #value error.
Any help would be greatly appreciated.
Darren
This blog is phenomenal. I am stumped. I have an account number for my customers is column A and Column B lists the sale amount. I would like to have column c aggregate the sales for each customer. Some customers have 1 sale each month some have 10. How do I get a running total for customers in column c
Hi DallasJewel,
Is my understanding correct that there can be several occurrences of the same account number in column A if a customer has more than 1 sale? If so, you can use this formula:
=SUMIF(A:A, A1, B:B)
If you are looking for something different, please clarify.
what do you means different,
What a great Blog!
Im not great at excel but your explanations are so simple and easy to understand!!!
I do have a question though :-p I am currently using you're SUMIF formula for specific text.
=SUMIF('Main Tracker'!G7:G30,"*GBCDF*",'Main Tracker'!T7:T30)
I was wondering can you also add a date range to that formula so that it totals up everything with "GBCDF" within a certain date range.
Hello,
Here is my questions with the example
I have in F2 = "Apples/Bananas"
I need my formula to be =sumif(A2:A8,F2,C2:C8)
In F2 I can have whatever sign or space that make this formula works.
I need this formula to add Apples and Bananas.
Any thought or help ?
Thank you in advance
C.
I can't seem to get this to work. Is this formula legal or possible?
=SUMIF('ITSC-0101S-01:ITSC-0909S-01'!E3, B3, 'ITSC-0101S-01:ITSC-0909S-01'!B3)
What is supposed to do is look at cell E3 across multiple sheets and IF it matches cell B3 on the formula sheet then add the corresponding cell B3s together.
Dear
Greetings..!
I'm trying to calculate SUMIF function but i do not know how to calculate and getting my answer but i would expecting you guys will solve my problem in excel.
i have an excel sheet that contain in Column (A) Date as (2-12-2016 9:37 PM) in Column (B) Names Like (Adam, John, Wiki) in Column (C) an Amount such as 100, 200, 500 in Column (D) Should be My Answer that's are follows
if in Column (A) Date "1-1-2016 to 31-1-2016" and in Column (B) "Adam" then Calculate the amount in Column (C) "100" and show the Addition in Column (D)
i tried this but i'm fail
=SUMIFS(C1:C3,B1:B3,"Adam")+SUMIFS(C1:C3,B1:B3,">=1-01-2016",A1:A3,"<=31-01-2016")
Kindly Help me
Thanks in Advance
I want to do a monthly ytd total on a spreadsheet with the months in columns. I have a ytd column and am trying to put in the sumif forumla taking the months Jan - Dec and giving them a value of 1 - 12. I then, in the cell immediately above my formula, have the current month's value. My sumif function is =Sumif(h5:s5,"<=v8",h9:s9) my calculation would be then that it should add all of the columns numbers 1 - 11 as that is the number displaying in v8 and not add the number in the 12 column. However it is adding it. What am I missing?
Hi,
Thanks for this wonderful step-by-step teaching about Excel. I find it very useful.
I have a problem regarding SUMIF issue. Let me explain.
=SUMIF(Inc!$M$1:$M$1000, ">=1/2/16", Inc!$J$1:$J$1000)- SUMIF(Inc!$M$1:$M955, ">=7/2/16", Inc!$J$1:$J$1000)
How can I change ">=1/2/16" and ">=7/2/16" to a cell number like B2 or C3 that contains the particular date like 1/2/16. So, I have made 2 cells that have 1/2/16, and the other one is 7/2/16. Instead of having to input the date manually in the formula, I want to input the cell number that already has the date information in it so I can just copy it down.
Thanks
Iwan
please how can i have continous additions of numbers each time new numbers is imputed in excel
I have a column of finishes, I only want to count the total finishes below 41.
Hi how can i calculate sum of range if the range has to be given by user.
If i give 5 in a cell and i need sum of range from a1 to a5 in my result cell B2
And if i give 7 result should be sum of a1 to a7
AND IS THERE ANY OPTION PROCESS ETC THROUGH EXCEL CAN FLASH OR REMIND 15-30 MINUTES BEFORE ANY MEETING ETC
IN A FROM 1-31 IS DATE N IN B C D ... IS THE NAME OF THE PERSON
IF B IS PRESENT IN DAY 1 I HAVE TYPE Y AND IF NOT THEN I HAVE TYPE N. NOW I WANTS TO SUM THE HOW MANY DAYS B OR C OR D WAS ABSENT OR PRESENT HOW CAN I GET THE RESULT IN LAST CELL/COLUMN NO IN 32 NO CELL