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)
343 comments
Hi?
Kindly help i have data of overheads with diferent dates now i want to sum up using the sum if functions overheads per month eg
Electricity 2/04/2015 $200
electricity 3/04/2015 $215
courier cost 2/04/2015 $2
Basically i want to come up with a spreadsheet that can be able to sum up overheads say march electricity was eg $700 travel Expenses may $600
Thanks in advance
Bismark
Hello Bismark,
If you want to consider just the month, you can use one of the following formulas to calculate the expenses, e.g. in March:
=SUMIF(B1:B3,">="&DATE(2015,3,1),C1:C3)-SUMIF(B1:B3,">="&DATE(2015,3,31),C1:C3)
=SUMIF(B1:B3,">=03/01/2015",C1:C3)-SUMIF(B9:B14,">=03/31/2015",C1:C3)
=SUMIFS(C1:C3,B1:B3,">=03/01/2015",B1:B3,"<=03/31/2015")
If you want to consider the value in column A as well, e.g. "electricity", please use the SUMIFS function:
=SUMIFS(C1:C6,B1:B6,">=03/01/2015",B1:B6,"<=03/31/2015",A1:A6,"electricity")
i just want to sum all in one merge cell only
Thanks You;
1 Vikash 50 ?
50
50
50
2 Mahesh 100 ?
100
100
100
3 Rakesh 500 ?
500
500
I want to sum these values where is the question Mark (?) in excel with single formula.Please help me.
I have an Excel 2010 worksheet containing a separate "$ amount" column for each of 3 entities (column headers: MD, MDM & MDW).
The data is rows of direct debit amounts from the company's bank accounts that are specified in 3 "Frequency"-header columns ("MTHLY", "QTRLY" or "ANNUALLY").
I have auto-summed each entity column, so have an overall direct debit total for each of the 3 entities (MD, MDM & MDW).
However, I need to auto-calculate the total - OF ALL 3 ENTITIES TOGETHER - for each of the 3 frequency columns (so $ amount total for "monthly", "quarterly" & "annual" direct debits)
I have wasted the last couple of hours trying to find a formula for this & have tried a variety of SUMIF cell combinations/formulas, but cannot get this to work!!!!
Can you please advise?
Hi,
I have a spreadsheet where I am trying to work out a formula to tell me how many cells have a date that is older than a year from today. At the bottom of the training date column I'd like to see a figure that tells me how many of the cells have a date older than one year.
Are you able to help please?
Training Date
01/06/2014
03/06/2015
01/01/2014
01/10/2014
05/06/2014
09/08/2014
01/03/2014
Hi Rae,
You can use the DATEDIF function to calculate the number of complete years between the dates in, say, column A and TODAY(). And then add up those that are equal to or greater than 1:
=SUMPRODUCT((DATEDIF(A2:A100, TODAY(),"y")>=1)*1)
I'm not sure if I have the right topic, but I'm trying to subtract the larger number from 2 cells (not a range, eg. A1 and A3) and subtract them from a number in cell A5. I just can't seem to find a formula that works.
Hi Sean,
Here you go:
=IF(A1>A3, A5-A1, IF(A3>A1, A5-A3, ""))
Just notice that the formula will return an empty string if A1=A3.
Thank you so much!
How would I get the sum formula to sum two numbers. I need the smallest number out of Coulum O through R and need that to be added with the number from column S. I just need the sum formula to decipher the smallest number from the range of colums.
Hi beth,
You can use a formula similar to this:
=MIN(O2:R10)+MIN(S2:S10)
I am wanting to sum cumulative values across cells if the last cell in each formula is greater than 0.
e.g sumif(C18:F18,F18>0)
F18 being the last cell in the formula, and the next one would be (C18:G18,G18>0) and so on
This is for an actuals vs forecasted spend graph of invoices.
Hi Sarah,
I believe you can use this one:
=if(F18>0, SUM($C$18:F18), "")
That works perfectly, thank you! :)
I am trying to sum numbers that occur at specific times (4/20/15 2:01 PM, 2658
4/20/15 2:04 PM, 2268, etc.)
but I only want to sum them if they occur during another time window specific in a third column. Is there a way to do this? The goal is to try to take random time periods and make them more uniform (15 minute intervals) by summing results during the random time period.
Hi Rebecca,
You can try using the COUNTIFS formula similar to this:
=COUNTIFS(A1:A11, "04/20/2015 2:01:00 PM", B1:B11, 10)
Where column A contains times, B - intervals, and 10 is the time interval you want to count.
I want to find the sum of the QUANTITY of any row that has a particular text string present in 1 or more columns of that row.
Example:
Row 1 has a quantity of 1,000 and has the word:
"Apple" in 2 of the 5 criteria columns
"Plum" in 1 of the 5 criteria columns
Row 2 has a quantity of 2,000 and has the word:
"Apple" in 1 of the 5 criteria columns
"Plum" in 0 of the 5 criteria columns
The resulting quantity for "Apple" would be 1,000 + 2,000 because both Rows 1 and 2 have the word "Apple" in at least one of the 5 criteria columns.
The resulting quantity for "Plum" would be 2,000 because only Row 2 has the word "Plum" in any of the 5 criteria columns.
Hi Dianne,
You can add up 5 SUMIF functions, like this:
=SUMIF(B2:B100, "apple", A2:A100) + SUMIF(C2:C100, "apple", A2:A100) + SUMIF(D2:D100, "apple", A2:A100) + etc.
Hi,
Reg SUMIFS formula.
My criteria range include numbers but they are in text format. Such as 00001,00002. Since these numbers are coming from ERP system report, format changing is quite difficult. Please let me know a solution for this. Sum range is OK. My formula is not working because criteria range is in text format. please help.
Hi Rasika,
Did you try enclosing those text-numbers in double quotes like usual text values? E.g.:
=SUMIF(range, "00001", sum_range)
I am trying to match amounts from two different datasets. I have cell A1(GL acct #) B1(trial balance $ amt) and then from an entirely different system with the exact same format A1(GL acct #) B1(trial balance $ amt). These GL accts are mapped to one another, the issue with my sumif is that it will pull the return the same $ for multiple lines. This is because in some cases 4 different GL accts from system 1 map into only 1 GL acct from system two.
I want my sumif to only return the amount ONCE, as opposed to every time it comes across a match. A nested sumif is my thought, saying something like IF((___ has not yet appeared)then(sumif(yadayadayada))else(return(n/a)) but i cant figure it out.
I have a column of a range of dollars and another column with a range of hours.
I want to have a function whereby if the dollars fall between two values then it returns of a sum of the corresponding hours.
I.E if the $ are equal to or between $3000 and $5000 then add the hours in the Hours column which correspond to the dollar rows.
I am sure this can be done - but how?
Best wishes
RJ
Hello Rupert,
You can use the SUMIFS function to sum values in the column with hours when the dollar values fall between two numbers:
=SUMIFS(B12:B20,A12:A20,">3000",A12:A20,"<5000")
Here column B is the one with hours and column A contains the range of dollars.
Hi,
I need an Excel VBA function to look for numbers in range of cells that contain both numbers and text and sum those numbers.
Hi,
I want to look for any cell that contains number in celles range and sum thos numbers?
Hi, I have three columns, A, B and C:
Col A
36
0
253
Col B
2
0
3
For Column C, I need a formula that will provide the sum of the corresponding cell in Column B, except if the corresponding (row) number in Column A is between 0 and 43. In that case, the number in Column C should be a "1".
So instead of Column C being 2, 0, 3, it would be 1, 0, 3. Can you possibly help??
Thank you in advance.
Hello Lory,
If we understand your task correctly, here is the formula you need:
=IF(AND(A1>0,A1<43),1,B1)
Hi,
I am trying for a formula where I am summing of cells and it 8th not showing the exact value due to negative value. I don't want to count the cell in negative or #ref!.Please help me with formula.
Hello Deepak,
You can use the following formula to exclude negative numbers:
=SUMIF(B1:B8,">=0",B1:B8)
Here B1:B8 is the range of cells you want to sum.
First, let me say thanks for and good work on a well written tutorial. I found this page by googling "excel sumifs not equal to text" and it has been helpful, but I'm not quite to what I need.
Suppose in your example "Using comparison operators with cell references" that you wished to sum items not in a range instead of not in a single cell - how would the syntax change instead of ""&F1 ? If column G had a list of 3 different fruit, is there a way to do G1:G3?
I tried a couple things, and they didn't seem to work. I could achieve the desired result by using a sumifs statement with each individual cell called out in an additional criterion, but if I had a list with more than just a few cells, adding several criteria would quickly become tedious. Any advice would be great. Thanks!
Hi Greg,
Thank you so much for your kind words. I am afraid I cannot suggest any way other than a SUMIFS formul such as:
=SUMIFS($B$2:$B$9, $A$2:$A$9, "<>"&G1, $A$2:$A$9, "<>"&G2, $A$2:$A$9, "<>"&G3)
Thank you for the response! I've used SUMIFS to get this affect, but my concern is that if column G has 20 entries, the formula will become rather long (or, because the entire column cannot be excluded, adding an entry will require a change to the formula). After some further research, it looks like I'll be able to get there with a complicated SUMPRODUCT statement including ISNUMBER and MATCH. I will try that for my application and see where it goes.
Thanks again!
Is this possible?
I want to sum amounts from criteria contained in more than one column:
1. Criteria = is equal to a credit card number (This column that contains the credit card number)
2. Criteria = is equal to “NO” (This column contains [yes or no] and it indicates whether or not the card has been paid)
3. Sum_Range = I want to include the “No” criteria sums
Hi Dub,
You need SUMIFS rather than SUMIF in this case. For example:
=SUMIFS(C2:C100, A2:A100, "card number", B2:B100, "no")
Where C is the sum range, A is credit card numbers and B is yes/no column.
Please check out the following tutorial for more details and examples of using SUMIFS in Excel:
https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
I have a table with a column for insurance company, a column for type of insurance, and a column for the premium paid for that policy. I'd like to do a conditional total IF Column A = "Erie" AND Column B = "Auto" THEN print the sum of those cells in Column C that meet both criteria. I'm thinking that I need to nest functions, but not sure the correct syntax for excel. Thanks!
Hi Shawnda,
You can use the SUMIFS formula similar to this:
=SUMIFS(C2:C100, A2:A100, "Erie", B2:B100, "Auto")
Am very grateful 1,000 000 thanks...
I got it... thanks.
I am working on a spreadsheet that tallies up donations (cans) per day but I want to have one cell tally for every 10 cans donated they receive 1 pt.
What formula should I use?
Thanks.
Hi,
Using your given example.
Is it possible to copy to a cell all the products with the same quantity?
In order to list every product with quantity higher then X.
Thanks in advance.
Best regards,
José
Hi Jose,
The easiest way to go would be filtering your data by quantity and copying the records you get.
You can also use the VBA code that one of our developers wrote for you; please follow these steps:
- Open your Excel file
- Press Alt+F11 on your keyboard;
- Double-click the sheet name in the list and paste the code below, but change the reference to your data range and to the cell with the quanity criterion:
Private Sub CommandButton2_Click()
Range("F1:G10").Select
Selection.ClearContents
i = Range("D2").Value
Range("A1:B10").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$10").AutoFilter Field:=2, Criteria1:=i
Range("A1:B10").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Selection.AutoFilter
End Sub
- Click Run.
You can see a sample spreadsheet with this solution here.
Hi,
This is really very much helpful.
I have a data of a period of one year(with Date mmddyyyy format). Is it possible to use the SUMIFS function to get the first criteria as month against the date range and one second criteria to get sum of expenses
Thanking You...
Tonis
Hello Tonis,
If we understand your task correctly, you can use either SUMIF function:
=SUMIF(A2:A33,">="&DATE(2015,2,1),B2:B33)-SUMIF(A2:A33,">"&DATE(2015,3,1),B2:B33)
or SUMIFS function:
=SUMIFS(B2:B33,A2:A33,">="&DATE(2015,2,1),A2:A33,"<"&DATE(2015,3,1)) Here column B is the one with expenses, and column A contains the dates.
is it possible to use 'sumif'formula to sum the similar colored cells?
Hello John,
We have a special add-in that can sum values in cells by their color:
https://www.ablebits.com/excel-count-sum-color/index.php
Hello
I want to sum invoice ammounts that our clients owe us. Some are due payments in 90, 60 or 30 days from invoice date. The fields are: client name, invoice date, total invoice amount and invoice state. Example: ACME, $3.567, 12-25-2014, pending.
Is there any way to have update owed amounts based on today() that should be paid because 90 days since invoice date have occurred?
Thank you
Hello Tomeu,
You can use Conditional Formatting to highlight the necessary amounts: create a rule and choose to "Use a formula to determine which cells to format", enter the following one:
=AND(TODAY()-B2>90,D2="pending")
Here B2 is the invoice date and D2 is the invoice state.
You can also use a helper column with the following formula to show the amount if both conditions are true:
=IF(AND(TODAY()-B2>90,D2="pending"),C2)
how can I use SUMIF to add numbers in a cell with out the letter as in 8T, here is my formula so far
=SUMIF(C10:AG10,"<8",C13:AG13) here is the problem.
in C10-AG10 I have a number 8 or less, in C13-AG13 I have a number and letter 8T, 7T, 6T, 5T, 4T,... down to 0.5T and I need to add the number but not the "T", how can I modify the SUMIF statement to do this, the cells are formatted Text, I have put the cells to number but if I put in the "T" then it doesn't work, I need the letter to identify what kind of number it is.
Hello Mac,
Try the following array formula (press Ctrl+Shift+Enter to complete it)
=IFERROR(SUM( --(C10:AG10<8)*(IF(LEN(C13:AG13)>1,LEFT(C13:AG13,LEN(C13:AG13)-1),0))),0)
If it is not the case, you can send your workbook to support@ablebits.com. We’ll look into the issue and try to help.
i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1-jul-2015 with multiple years being listed.
date cash check
1-jul-2015 50.00 0
1/jan-2014 0 100
1-feb-2105 0 75
total for 2015 should be 125.00
Hello Brian,
As SUMIF and SUMIFS sum values only if the ranges are of the same size, you can add two SUMIFS:
=SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")+SUMIFS(C1:C3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")
Here B1:B3 is the range with cash amounts, C1:C3 is the range with check amounts, and A1:A3 is the range with dates.
I'm trying figure out what formula to use. I have two columns of numbers. I want a formula that does the following:
Whatever cell in column A is in the same row as column B, when the sum in column B whichever cell is greater than or equal to the value of another cell). ***SUM(B1:B-whichever cell is greater than or equal to the value of another cell)***
Thanks!
Hello Grant,
Could you please send a sample worksheet with the data and the expected results to support@ablebits.com? It will help us understand your task better.
Please include the name of the blog post and the number of your comment.
Hi,
I am working on a financial model, and i have may cash flow mapped monthly.
Now i would like to analyze the information on a trimester basis.
Please can you assist with a formula that allows me to sum the monthly amount to a trimester amount.
Regards
Hello Thany,
You can create SUMIFS formula and use date ranges as criteria:
Trimester 1:
=SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=01/10/2015")
Trimester 2:
=SUMIFS(B1:B3,A1:A3, ">=1/11/2015",A1:A3, "<=01/20/2015")
Trimester 3:
=SUMIFS(B1:B3,A1:A3, ">=1/21/2015",A1:A3, "<=01/31/2015")
I NEED HELP WITH A FORMULA.
I HAVE A SPACE THAT IS FILLED WITH AN AMOUNT.
BELOW I HAVE ANOTHER SPACE THAT HAS THE TOTAL.
INBETWEEN THOSE SPACES I NEED TO BUT A SQUARE WITH THE FORMULA THAT HELP ME ADD A CERTAIN NUMBER IF THE TOP SPACE REACHES TO OR OVER THAT CERTAIN NUMBER.
I.E
SQUARE ONE-5.00
SQUARE TWO- FORMULA THAT READ: IF SQUARE ONE REACHES 5.00 OR OVER, THEN .50 CENTS IS ADDED ON.
SQUARE THREE- WOULD READ THE AMOUNT BELOW 5.00 OR THE AMOUNT ABOVE 5.OO PLUS EXTRA 50. CENTS
THANKS FOR THE HELP
Hello Michael,
You can use IF function to add a certain amount by your condition, e.g.:
=IF(C5>=5,C5+0.5,C5)
Here cell C5 is the amount you want to check. If it is more or equal to 5, the formula will show the value from cell C5 plus 0.50, otherwise it will show just the value from cell C5.
As for square three, could you please describe the condition and the expected result in more detail?
IN COLUMN J SOME CELL I ALREADY ADDED LIKE J54=SUM(J1:J53)AND J100=SUM(J60:J99)AND J130=SUM(J105:J129).LIKE THIS I ADDED IN K,M,O,Q.....ETC COLUMNS. NOW I ADDED THESE (J54,J100&J130) IN B350=J54+J100+J130.HOW TO PUT THE FORMULA FOR ADDING K,M,O,Q..ETC IN B351,B352,B353,B354..ETC
Thanks for time range formula! It worked.
I want to add c2 and d2 together with the total in e2. However i only want them to add if neither of the numbers entered in c2 or d2 are zero.
Hello Jackie,
You can use the following formula is cell E2:
=IF(AND(C2<>0, D2<>0), C2+D2, "")
I have fruits list in column A1 to A3,A1=mango,A2=apple and A3=banana.How we type by formula their price in column B1 to B3. mango=200,aplle=150 and banana=100. if i have the very lagre list.for example A1 to A100 of different types of fruits and their different amount by putting any formula and easy formula.all columns from B1 to B100 fill spontaneously.
please reply
Hello Faisal,
Could you please specify if you want to fill column B with random numbers, or if you have another range to take the prices that correspond to the fruit name?
If you want to fill the cells with random numbers within a certain range, you can use our Random Generator add-in:
https://www.ablebits.com/excel-random-generator/index.php
If you have a range with the prices, please specify where it is, or send a sample spreadsheet to support@ablebits.com. If you send your Excel file to us, please add a note with the name of the article and the number of your comment.
Hi, great blog.
I have a challenging problem:
I have data in a large, changing table, with I need to get a percentage readiness for:
e.g.
Resource; A; B; C; D; E; F; etc
Minimum Number needed; 1; 3; 2; 5; 8; 2; etc
Team 1; 1; 2; 2; 8; 8; 8; etc
Team 2; 3; 3; 1; 4; 8; 8; etc
Team 3;
Team 4 etc
I am trying to find out for each team what the percentage readiness is, i.e.
Team 1, Resource A shows they have one out of a required one, Resource B two out of three; Resource C two out of two. Resource E eight out of five (so more than they require) etc.
The maximum number to use should be the Resource – i.e. Team 1, Resource A = 1/1 = 100%, B = 2/3 = 66%, C = 2/2 = 100%, D = 8/5 = 100% etc. In Total, 20 out of 21.
I am trying to do this without a “helper table”.
Can you help?
Hi, great blog.
I have a challan
Hi Ben,
Thank you very much for your feedback.
You need to set the cell format to percentage first, and then use the following kind of formula in the table cells:
=IF(B4/B3>1,1,B4/B3)
Here B4 is the cell with the actual number Team 1 has, B3 is the Minimum number required under Resource A. The same for Resource B would be:
=IF(C4/C3>1,1,C4/C3)
Just enter the formula and copy it across the table to adjust cell references.
I am trying to get a formula to work that says if the account number, and the stock code match, then pull data in from another tab. I had it working on an old sheet, but I can't find it.. I've been playing around with it for a while.
Basically I have this on Sheet1:
Account Number
ABC
Qty Stock Code Price
123 $1
456 $2
and this on Sheet 2:
Account Number Stock Code Quantity
ABC 123 750
ABC 456 120
and I'm trying to get it to say for account number ABC, there is 750 of stock code 123, then stock code 456 there is 120.
I have got data coming from all over the place, but this one is just not working. I think I have been looking at it too long :( could you please help?
Thanks so much!
Hello Bella,
There are three ways you can do this.
1) You can use the VLOOKUP function:
- Create a helper column in Sheet 2 that will concatenate the Account number and Stock code, e.g. =A2&" "&B2
This way you will get a lookup value of this kind: "ABC 123"
- Enter a formula with the VLOOKUP function to the column with Qty in Sheet1:
=VLOOKUP(A2&" "&C2,Sheet2!C2:D35,2,FALSE)
It looks for the concatenated "Account number" and "Stock code" from Sheet1 in Sheet2, and returns the value from column D, which is the second from the left.
You can find a detailed description of using multiple cells as lookup criteria in this blog post.
2) You can use the combination of INDEX and MATCH functions,
- Create a helper column in Sheet 2 that will concatenate the Account number and Stock code, e.g. =A2&" "&B2
- Enter the following function to get Qty from Sheet2:
=INDEX(Sheet2!D1:D35,(MATCH(Sheet1!A2&" "&C2,Sheet2!C1:C35)))
Please see this blog post for more details.
3) You can use our Merge Tables Wizard add-in:
- Select Sheet 1 on step 1
- Select Sheet 2 on step 2
- Select "Account number" and "Stock code" as key columns to compare
- Choose "Qty" as the column to update on step 4
- Pick any additional settings you like on the last step and click Finish.
Wonderful blog!
The first example is what I need help with - SUMIF(range, criteria, [sum_range]. I need to do exactly as described, for a report on Sheet 3. Here is the challenge:
Sheet 1 has the range and the [sum_range] , but the text criteria (also on sheet 1) is a drop down list with the source list on Sheet 2.
How do I reference the text (for report on sheet 3) in the drop down box (sheet 1) as the criteria?
Thank you!
Hello Sandy,
Thank you very much for your feedback!
If you want to refer to the cell with a drop-down list as to changing criteria, you can enter its address just like you would enter any other cell address.
If you want to create a drop-down list for the report in Sheet 3 and see the results of the SUMIF function depending on the selected value, you can add the same data validation list on Sheet 3 and refer to it in your formula, e.g.:
=SUMIF(Sheet1!D1:D6,D1,Sheet1!C1:C6)
Here D1 is the cell in Sheet 3 that contains the drop-down list. When you change the value in it, the results will be updated accordingly.
I am trying to return a value when I have different time frames in column c (24 hour period - different minutes every time, example: 0415, 0423, 0439, 0501 military time, each time frame has a number of passengers arriving on that hour. I tried using =SUMIF(C2:C12,"04",B2:B12)to return how many passengers are arriving between 0400 hours and 0459 hours, but all that the formula returns is the sum of the first 0400 line (10), instead of returning the sum of all flights that start with "04". Can you help? Thanks.
Hello Charlie,
The SUMIFS function works a treat, the formula goes as follows:
=SUMIFS(B2:B12,C2:C12,">="&TIMEVALUE("04:00"), C2:C12,"<"&TIMEVALUE("05:00"))
This function sums B2:B12 for the time interval 4:00:00 to 4:59:59. Naturally, you can specify other time intervals in the formula if needed.
For more information about the SUMIFS function, please see:
https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
Spent 30 minutes trying to display a a sum of a range when another cell expressed a specific word. You solved it.
Formula: =SUMIF(C1:C168,"AnPac Insurance",D1:D168)
Thank you
I'm trying to create a formula that would add together the tax free price of my sales and the VAT to total my income.
So far the Formula looks like =SUMIF(A5;"x";E5:F5)
On the A column I will mark an "x" to indicate that the bill has been paid. Colum E has the tax free price and column F has the VAT.
The formula will not add the two together but give me the value of Column E as the total sum (i.e. missing the value from column F).
What am I doing wrong?
Hello Antti,
I am sorry, it is difficult to recommend you anything without seeing your data. Please post this question on our forum with a sample workbook attached and our support team will try to help.
I have a table containing data (a number of transaction Column A1 date showing i.e., Wednesday, November 11, 2014. and in column B Sales amount) now from that table i want to sum all the sales occured in Wednesday. is it possible?
Hi Obaydul,
Yes, it is possible. You can use the following array formula (remember to press Ctrl + Shift + Enter to complete it):
=SUM( (--WEEKDAY($A$2:$A$100)=4)*($B$2:$B$100))
The WEEKDAY function takes Sunday as 1 by default, so 4 stands for Wednesday.
In Excel sheet we have a marksheet having 10 students. Column headings are Name, Hindi , Eng, Maths and Total.
Using Sum Function we have find out Total marks obtained. Next we have found out Maximum marks using MAX function. Is it possible in Execl to print Name of student who has secued Maximum marks along with maximum numbers. i.e. Max marks along with candidate name
I am trying to create a formula across rows for a work holiday time off calendar. I want to create for each person that it reads each month across the rows that if they are on holiday, we can put a H on those dates and the formula will read this and take the total number of holiday days down.
I can do this when the month is in columns but not sure how to get the formula to work if it is in rows.
Hope this makes sense?
Hello Clare,
You can use the following kind of formula to count the number of cells that contain H:
=COUNTIF(B1:B31,"=H")
The formula above will check cells 1 to 31 in column B; you can change the range to row or column depending on how your data are organized.
Sorry that should look like this
sheet one
Fruit No Boxes Colour supplier
apple 2 green jones
pear
orange
apple 4 red smith
lemon
sheet two
apple 2 green jones
apple 4 red smith
Hello John,
Could you please specify what you would like to get as a result? If you want to pull information based on the same value in column A, e.g. "apples", then the VLOOKUP function or our Merge Tables Wizard add-in will help you.
If you want to find repeating values in several columns, please see this article.
If your task is different, you can send a sample spreadsheet with the way you have your data and the way you want to see it in the result to support@ablebits.com. Please include the name of the article and your comment number. We'll do our best to assist you.
Hi
I have an excel workbook with several sheets what i like to do is display information from one sheet onto another
IE if the word apples is displayed a column i want the information displayed in the row containing the word apple
Sheet one sheet Two
apple boxes, 2 type, green supplier, jones apple boxes 2 type green supplier jones
pear apple boxes ,4 type, red supplier, smith
orange
apple boxes ,4 type, red supplier, smith
lemon
Many thanks John
I am facing a difficulty where I have many data in different rows and columns,but I cant seemed to have this function valid for it. Can you please suggest a formula or point me in the right direction? Thank you!!
E.g: Data which i need to lookup to is from Jan - Dec. But i have 3 different data sets to lookup from , which is actual numbers, historical numbers and budgeted numbers for each single month, on my left column. Apart from that, on my left column, i do have PLANT A, PLANT B, PLANT C which has that 3 actual/historical/budgeted numbers.
Hi George,
Sorry, it's a bit hard for me to understand the structure of your data. If you can post a data sample with column letters and specify what exactly result a formula should return, we'll try to help.
I am trying to create a formula where there is always a fixed price ($70). Every bathroom is $10. Every square foot over 700 is $0.10. I've been trying to create a formula that adds the fixed price, price per bathroom, and price for square foot over 700, but I can't figure it out. Any feedback would be great!
Hi Caroline,
If you can tell me a bit more about your data structure, I'll try to work out a proper formula for you. In particular, in what columns do you have all those prices and item descriptions?
If you can post a sample of your data and the expected result, this would be even more helpful.
This is a really helpful way for students to have free time and study propular on the questions that is serious for them to know about.
In a certain column which records date,let few cells have left empty(without any date).
so, in that case what to do if we want to autofilled them "no date given" in those cells.In what way we sud use conditional stat.
Hello Manish,
Please have a look at the following article that explains how to fill empty cells in Excel, hopefully, it will prove helpful:
https://www.ablebits.com/office-addins-blog/fill-blanks-excel/#fill-cells-zero