by This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel 365, 2021, 2019, 2016, 2013, 2010, and lower.
As everyone knows, Microsoft Excel provides an array of functions to perform various calculations with data. A few articles ago, we explored COUNTIF and COUNTIFS, which are designed for counting cells based on a single condition and several conditions, respectively. Last week we covered Excel SUMIF that adds values meeting the specified criteria. Now it's time to go over the plural version of SUMIF - Excel SUMIFS that allows summing values by multiple criteria.
Those who are familiar with the SUMIF function might think that converting it to SUMIFS takes just an extra "S" and a few additional criteria. This would seem quite logical… but "logical" it's not always the case when dealing with Microsoft : )
Excel SUMIF function - syntax & usage
The SUMIF function is used to conditionally sum values based on a single criteria. We discussed the SUMIF syntax in detail in the previous article, and here's just a quick refresher.
- range - the range of cells to be evaluated by your criteria, required.
- criteria - the condition that must be met, required.
- sum_range - the cells to sum if the condition is met, optional.
As you see, the syntax of the Excel SUMIF function allows for one condition only. And still, we say that Excel SUMIF can be used to sum values with multiple criteria. How can that be? By adding the results of several SUMIF functions and by using SUMIF formulas with array criteria, as demonstrated in the examples that follow.
Excel SUMIFS function - syntax & usage
You use SUMIFS in Excel to find a conditional sum of values based on multiple criteria. The SUMIFS function was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, 2013, 2016, 2019, 2021, and Excel 365.
Compared to SUMIF, the SUMIFS syntax is a little bit more complex:
The first 3 arguments are mandatory, additional ranges and their associated criteria are optional.
sum_range- one or more cells to sum, required. This can be a single cell, a range of cells or a named range. Only cells with numbers are summed; blank and text values are ignored.criteria_range1- the first range to be evaluated by the associated criteria, required.criteria1- the first condition that must be met, required. You can supply the criteria in the form of a number, logical expression, cell reference, text or another Excel function. For example you can use criteria such as 10, ">=10", A1, "cherries" or TODAY().criteria_range2, criteria2, …- these are additional ranges and criteria associated with them, optional. You can use up to 127 range/criteria pairs in SUMIFS formulas.
Notes:
- For a SUMIFS formula to work correctly, all the criteria_range arguments must have the same dimension as sum_range, i.e. the same number of rows and columns.
- The SUMIFS function works with AND logic, meaning that a cell in the sum range is summed only if it meets all of the specified criteria, i.e. all the criteria are true for that cell.
Basic SUMIFS formula
And now, let's have a look at the Excel SUMIFS formula with two conditions. Suppose, you have a table listing the consignments of fruit from different suppliers. You have the fruit names in column A, suppliers' names in column B, and quantity in column C. What you want is to find out a sum of amounts relating to a given fruit and supplier, e.g. all apples supplied by Pete.
When you're learning something new, it's always a good idea to start with simple things. So, to begin with, let's define all the arguments for our SUMIFS formula:
- sum_range - C2:C9
- criteria_range1 - A2:A9
- criteria1 - "apples"
- criteria_range2 - B2:B9
- criteria2 - "Pete"
Now assemble the above parameters, and you will get the following SUMIFS formula:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
To refine the formula further, you can replace the text criteria "apples" and "Pete" with cell references. In this case, you won't have to change the formula to calculate the quantity of other fruit from a different supplier:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Note. Both the SUMIF and SUMIFS functions are case-insensitive by nature. To get them to recognize the text case, please see Case-sensitive SUMIF and SUMIFS formula in Excel.
SUMIF vs. SUMIFS in Excel
Since the aim of this tutorial is to cover all possible ways to sum values by several conditions, we will discuss formula examples with both functions - Excel SUMIFS and SUMIF with multiple criteria. To use them correctly, you need to clearly understand what these two functions have in common and in what way they are different.
While the common part is clear (similar purpose and parameters), the differences are not so obvious, though very essential.
There are 4 major differences between SUMIF and SUMIFS:
- Number of conditions. SUMIF can evaluate just one condition at a time while SUMIFS can check for multiple criteria.
- Syntax. With SUMIF, the sum_range is the last and optional argument - if not defined, the values in the range argument are summed. With SUMIFS, sum_range is the first and required argument.
- Size of ranges. In SUMIF formulas, sum_range does not necessarily have to be of the same size and shape as range, as long as you have the top left cell right. In Excel SUMIFS, each criteria_range must contain the same number of rows and columns as the sum_range argument.
For example, SUMIF(A2:A9,F1,C2:C18) will return the correct result because the leftmost cell in the sum_range argument (C2) is right. So, Excel will make the correction automatically and include as many columns and rows in sum_range as there are in range.
A SUMIFS formula with unequally sized ranges will return a #VALUE! error.
- Availability. SUMIF is available in all Excel versions, from 365 through 2000. SUMIFS is available in Excel 2007 and higher.
Alright, enough strategy (i.e. theory), let's get into the tactics (i.e. formula examples : )
How to use SUMIFS in Excel - formula examples
A moment ago, we discussed a simple SUMIFS formula with two text criteria. In the same manner, you can use Excel SUMIFS with multiple criteria expressed by numbers, dates, logical expressions, and other Excel functions.
Example 1. Excel SUMIFS with comparison operators
In our fruit suppliers table, suppose, you want to sum all deliveries by Mike with Qty. 200 or more. To do this, you use the comparison operator "greater than or equal to" (>=) in criteria2 and get the following SUMIFS formula:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Note. Please pay attention that in Excel SUMIFS formulas, logical expressions with comparison operators should always be enclosed in double quotes ("").
We covered all possible comparison operators in detail when discussing Excel SUMIF function, the same operators can be used in SUMIFS criteria. For example, the following formula with return the sum of all values in cells C2:C9 that are greater than or equal to 200 and less than or equal to 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Example 2. Using Excel SUMIFS with dates
In case you want to sum values with multiple criteria based on the current date, use the TODAY() function in your SUMIFS criteria, as demonstrated below. The following formula sums values in column D if a corresponding date in column C falls within the last 7 days, including today:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Note. When you use another Excel function together with a logical operator in the criteria, you have to use the ampersand (&) to concatenate a string, for example "<="&TODAY().
In a similar fashion, you can use the Excel SUMIF function to sum values in a given date range. For example, the following SUMIFS formula adds the values in cells C2:C9 if a date in column B falls between 1-Oct-2014 and 31-Oct-2014, inclusive.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
The same result can be achieved by calculating the difference of two SUMIF functions, as demonstrated in this example - How to use SUMIF to sum values in a given date range. However, Excel SUMIFS is much easier and more understandable, isn't it?
Example 3. Excel SUMIFS with blank and non-blank cells
When analyzing reports and other data, you may often need to sum values corresponding either to empty or non-empty cells.
| Criteria | Description | Formula Example | |
|---|---|---|---|
| Blank cells | "=" | Sum values corresponding to blank cells that contain absolutely nothing - no formula, no zero length string. | =SUMIFS(C2:C10, A2:A10, "=", B2:B10, "=")
Sum values in cells C2:C10 if the corresponding cells in columns A and B are absolutely empty. |
| "" | Sum values corresponding to "visually" blank cells including those that contain empty strings returned by some other Excel function (for example, cells with a formula like =""). | =SUMIFS(C2:C10, A2:A10, "", B2:B10, "")
Sum values in cells C2:C10 with the same conditions as the above formula, but includes empty strings. |
|
| Non-blank cells | "<>" | Sum values corresponding to non-empty cells, including zero length strings. | =SUMIFS(C2:C10, A2:A10, "<>", B2:B10, "<>")
Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, including cells with empty strings. |
| SUM-SUMIF or SUM / LEN |
Sum values corresponding to non-empty cells, not including zero length strings. | =SUM(C2:C10) - SUMIFS(C2:C10, A2:A10, "", B2:B10, "")
=SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0)) Sum values in cells C2:C10 if the corresponding cells in columns A and B are not empty, cells with zero length strings are not included. |
And now, let's see how you can use a SUMIFS formula with "blank" and "non-blank" criteria on real data.
Suppose, you have an order date in column B, delivery date in column C and Qty. in column D. How do you find the total of products that have not been delivered yet? That is, you want to know the sum of values corresponding to non-empty cells in column B and empty cells in column C.
The solution is to use the SUMIFS formula with 2 criteria:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Using Excel SUMIF with multiple OR criteria
As noted in the beginning of this tutorial, the SUMIFS function is designed with AND logic. But what if you need to sum values with multiple OR criteria, i.e. when at least one of the conditions is met?
Example 1. SUMIF + SUMIF
The simplest solution is to sum the results returned by several SUMIF functions. For example, the following formula demonstrates how to find the total of products delivered by Mike and John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
As you see, the first SUMIF function adds the quantities corresponding to "Mike", the other SUMIF function returns the amounts relating to "John" and then you add these 2 numbers.
Example 2. SUM & SUMIF with an array argument
The above solution is very simple and may get the job done quickly when there are only a couple of criteria. But a SUMIF + SUMIF formula may grow up enormously if you want to sum values with multiple OR conditions. In this case, a better approach is using an array criteria argument in the SUMIF function. Let's examine this approach now.
You can start by listing all of your conditions separated by commas and then enclose the resulting comma-separated list in {curly brackets}, which is technically called an array.
In the previous example, if you want to sum the products delivered by John, Mike and Pete, your array criteria will look like {"John","Mike","Pete"}. And the complete SUMIF function is SUMIF(C2:C9, {"John","Mike","Pete"} ,D2:D9).
The array argument consisting of 3 values forces your SUMIF formula to return three separate results, but since we write the formula in a single cell, it would return the first result only - i.e. the total of products delivered by John. To get this array-criteria approach to work, you have to use one more little trick - enclose your SUMIF formula in a SUM function, like this:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
As you see, an array criteria makes the formula much more compact compared to SUMIF + SUMIF, and lets you add as many values as you like in the array.
This approach works with numbers as well as with text values. For instance, if instead of the suppliers' names in column C, you had supplier IDs like 1, 2, 3 etc., then your SUMIF formula would look similar to this:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Unlike text values, numbers needn't be enclosed in double quotes in array arguments.
Example 3. SUMPRODUCT & SUMIF
In case, your preferred way is to list the criteria in some cells rather that specify them directly in the formula, you can use SUMIF in conjunction with the SUMPRODUCT function that multiplies components in the given arrays, and returns the sum of those products.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Where G2:G4 are the cells containing your criteria, the suppliers' names in our case, as illustrated in the screenshot below.
But of course, nothing prevents you from listing the values in an array criteria of your SUMIF function if you want to:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
The result returned by both formulas will be identical to what you see in the screenshot:
Excel SUMIFS with multiple OR criteria
If you want to conditionally sum values in Excel not simply with multiple OR conditions, but with several sets of conditions, you will have to use SUMIFS instead of SUMIF. The formulas will be very similar to what we've just discussed.
As usual, an example might help to illustrate the point better. In our table of fruit suppliers, let's add the Delivery Date (column E) and find the total quantity delivered by Mike, John and Pete in October.
Example 1. SUMIFS + SUMIFS
The formula produced by this approach includes a lot of repetition and looks cumbersome, but it is easy to understand and, most importantly, it works : )
=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "John", E2:E9, ">=10/1/2014", E2:E9, "<=10/31/2014") +
SUMIFS(D2:D9, C2:C9, "Pete", E2:E9, ">=10/1/2014" ,E2:E9, "<=10/31/2014")
As you see, you write a separate SUMIFS function for each of the suppliers and include two conditions - equal to or greater than Oct-1 (">=10/1/2014",) and less than or equal to Oct 31 ("<=10/31/2014"), and then you sum the results.
Example 2. SUM & SUMIFS with an array argument
I've tried to explain the essence of this approach in the SUMIF example, so now we can simply copy that formula, change the order of arguments (as you remember it is different in SUMIF and SUMIFS) and add additional criteria. The resulting formula is more compact than SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
The result returned by this formula is exactly the same as you see in the screenshot above.
Example 3. SUMPRODUCT & SUMIFS
As you remember, the SUMPRODUCT approach differs from the previous two in the way that you enter each of your criteria in a separate cell rather that specify them directly in the formula. In case of several criteria sets, the SUMPRODUCT function won't suffice and you will have to employ ISNUMBER and MATCH as well.
So, assuming that the Supplies Names are in cells H1:H3, Start Date is in cell H4 and End Date in cell H5, our SUMPRODUCT formula takes the following shape:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Many people wonder why use double dash (--) in SUMPRODUCT formulas. The point is that Excel SUMPRODUCT ignores all but numeric values, while the comparison operators in our formula return Boolean values (TRUE / FALSE), which are non-numeric. To convert these Boolean values to 1's and 0's, you use the double minus sign, which is technically called the double unary operator. The first unary coerces TRUE/FALSE to -1/0, respectively. The second unary negates the values, i.e. reverses the sign, turning them into +1 and 0, which the SUMPRODUCT function can understand.
I hope the above explanation makes sense. And even if it doesn't, just remember this rule of thumb - use the double unary operator (--) when you are using comparison operators in your SUMPRODUCT formulas.
Using Excel SUM in array formulas
As you remember, Microsoft implemented the SUMIFS function in Excel 2007. If someone still uses Excel 2003, 2000 or earlier, you will have to use a SUM array formula to add values with multiple AND criteria. Naturally, this approach works in modern versions of Excel 2013 - 2007 too, and can be deemed an old-fashioned counterpart of the SUMIFS function.
In the SUMIF formulas discussed above, you have already used array arguments, but an array formula is something different.
Example 1. Sum with multiple AND criteria in Excel 2003 and earlier
Let's get back to the very first example where we found out a sum of amounts relating to a given fruit and supplier:
As you already know, this task is easily accomplished using an ordinary SUMIFS formula:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
And now, let's see how the same task can be fulfilled in early "SUMIFS-free" versions of Excel. First off, you write down all the conditions that should be met in the form of range="condition". In this example, we have two range/condition pairs:
Condition 1: A2:A9="apples"
Condition 2: B2:B9="Pete"
Then, you write a SUM formulas that "multiplies" all of your range/condition pairs, each enclosed in brackets. The last multiplier is the sum range, C2:C9 in our case:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
As illustrated in the screenshot below, the formula perfectly works in the latest Excel 2013 version.
Note. When entering any array formula, you must press Ctrl + Shift + Enter. Once you do this, your formula gets enclosed in {curly braces}, which is a visual indication that an array formula is entered correctly. If you try typing the braces manually, your formula will be converted to a text string, and it won't work.
Example 2. SUM array formulas in modern Excel versions
Even in modern versions of Excel, the power of the SUM function should not be underestimated. The SUM array formula is not simply gymnastics of the mind, but has a practical value, as demonstrated in the following example.
Suppose, you have two columns, B and C, and you need to count how many times column C is greater than column B, when a value in column C is greater or equal to 10. An immediate solution that comes to mind is using the SUM array formula:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Don't see any practical application to the above formula? Think about it in another way : )
Suppose, you have the orders list like shown in the screenshot below and you want to know how many products have not been delivered in full by a given date. Translated into Excel's language, we have the following conditions:
Condition 1: A value in column B (Ordered items) is greater than 0
Condition 2: A value in column C (Delivered) in less than in column B
Condition 3: A date in column D (Due date) is less than 11/1/2014.
Putting the three range/condition pairs together, you get the following formula:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Well, the formula examples discussed in this tutorial have only scratched the surface of what Excel SUMIFS and SUMIF functions can really do. But hopefully, they have helped pointing you in the right direction and now you can sum values in your Excel workbooks no matter how many intricate conditions you have to consider.
666 comments
Hi,
EXPENSES Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Rent 32903 23984 11990 48653 4745 1720 24218 21844 11752 30100 25235 292
Salaries 42648 18557 16997 47318 43743 12539 12315 43353 26527 17536 41008 2324
Power 12778 43341 10086 31906 17087 47991 28095 25891 16000 7763 22037 1359
Fuel 33187 25257 30246 37761 836 16396 47184 46502 8481 28548 20981 43096
I want to know Jan month Salaries, using sumifs function
I have a large spreadsheet and need an array formula to average month end stock and place it as a starting stock for the next month. I have an array formula as follows {=AVERAGE(IF(R5&G5=$R$5:$R546&$G$5:$G546,$Q$5:$Q546))} but I cant get it to average the following {=AVERAGE(IF(R5&G5=$B$5:$B546&$G$5:$G546,$Q$5:$Q546))}
As you can see I am trying to get the formula to average all in column Q only for those cells in Q that have the same value in Column B & G. I get an answer when the same value is in the same column but i.e value needed to be the same is in the same column as the range but not when I need another reference from a difference cell? I hope I've explained myself
Can someone help me with the following?:
Col A Col B
$31.39 8.25%
$97.43 8.25%
$ 5.31 6.25%
What I'm trying to do is to create a formula where if Col B is 8.25% then sum Col A and divide by 1.0825, and if Col B is 6.25% then sum Col A and divide by 1.0625. Please help. Thank you!
Hi,
Can some body help me to sum the top 10 or 15 values with multiple conditions
I have a sheet 1 with 12 tables, 1 for each month, each table has has 2 rows, engineer name and total repairs. sheet 2 (Raw Data) has a list of each repair over the year, I want to take data from this to show on sheet one how many repairs each engineer did each month. So I need a formula that will sum or count where John did 32 repairs in Jan etc, so I guess I need to set criteria to count if John, if greater than 01/01/15 if less than 31/01/15 but I can't, I triend Sumif, sumifs, countif, countifs, sumproduct, vlookup, I just get the #value error, I can get it to count engineers or dates in the entire raw data sheet but not together. Any Help would be appreciated, my formula is, =COUNTIFS('Raw Data'!A2:A1500, "176WS", 'Raw Data'!H2:H1564, ">=01/01/2015", 'Raw Data'!H2:H1564, "<=31/01/2015")
in the row A, we have the names of customer, and B we have their deposit. using Sumif comment we can calculate their individual deposit easily, in onother colomn.
How can I use this comment as same
I need the sum of deposits except to two guyz (ex: ramu and Raju) from that 10 persons list. how can use command
ilike this function
Hi,
I am using a SUMIF formula against two columns (column 1 = customer name, column 2 = fees due), that adds the total fees due for a customer. I would like to be able to have my SUMIF formula in the third column and be able to drag fill the formula down the column without duplicate values appearing for the same customer. What would I need to add to my formula for this happen?
Thanks.
This is very good with understable examples..thanks a ton
Hi,
If I want to add all values in comum Q12:Q500 based on the date on column T12:T500 but also if the text matches to a certain criteria.
I have 30 associates and I want to separate in a date range by criteria.
I have this in my spreadsheet counting all without dates but I need to separate by date and criteria
=SUMIF(S:S,"CRITICAL",Q:Q)
"q" is the total criticals but I want to separate by date range on column "T" which has three different criterias
"Critical"
"Major"
"Minor"
can you help?
Very helpful - thank you!!!!
Hi,
I want use the formula for to get sum of column F:F as against
=SUMIFS($F:$F,$A:$A,$B$4:$B$101,$B:$B,$A4)
Here Critera1 is the range($B$4:$B$10)of anather sheet
not a single value , how can I modify the formula to get the sum
Here Critera1 is the range($B$4:$B$10)of another sheet
Here Critera1 is the range($B$4:$B$10)in of another sheet
Hi,
I want use the formula for to get sum of column F:F as against
=SUMIFS($F:$F,$A:$A,$B$4:$B$101,$B:$B,$A4)
Here Critera1 is the range($B$4:$B$10)
not a single value , how can I modify the formula to get the sum
Thanks & Regards,
Giri
Hi,
I would like to sum column D, providing that certain criteria are met. One of my criteria reference values in a range of cells.
Here is my formula,
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30))
could you please help me to use the range D1:D3 instead of typeing the values like{50861394,50861765,50861767}
Svetlana - this is fantastic. I bought Ablebits a while ago for several of the functions and I love this additional help!
I've been wanting to convert an investment tracking sheet from simple formulas that are time consuming to reproduce each cycle to advanced/array formulas that will do the heavy lifting each time once I give it the new data.
As such - this information, especially the SUM and SUMIF Array Argument, helped a great deal.
However, when I reproduced the array but changed SUM to AVERAGE and SUMIF to AVERAGEIF, it does not produce the correct average result. I know this as I worked it out the old way to make sure. I couldn't find a separate entry on AVERAGEIF/AVERAGEIFS, so I thought I'd ask here!
It also doesn't like it if I do COUNT/COUNTIF, too.
-Bret
Hi Bret,
Thank you for choosing Ablebits add-ins! As for AVERAGEIF/AVERAGEIFS, you read my mind - I'm working on this tutorial at the moment :)
If you can send us (support@ablebits.com) your sample workbook along with the expected result and we will try to figure out a proper formula.
Hi can you help me out with one of the figures
I have perticular set of alphabet numbers but each number carrying different colours , i want sort the colours with the diff numbers and required count
Thank you, Svetlana! I just sent that to you.
I would like to give sumif function for more than two citeria on the same cell
Example
150/48 R.Blue Int.
150/48 Int. T. Blue
150/36 Int. R. Blue
150/48 Int. C. Brown
150/48 R.Blue Int.
150/48 Int. C. Brown
Formular: i want to sum the total of R.Blue for 150/48 and 150/36
=IF(D7="","",SUMIF(Table,D7&"*",kgs)+SUMIF(Table,E7&"*",kgs)+SUMIF(Table,F7&"*",kgs)+SUMIF(Table,C7&"*",kgs))
giving sumif function for more than one citeria on one cell of text.
Regards
I would like to give sumif function for more than two citeria on the same cell
Example
150/48 R.Blue Int. 1
150/48 Int. T. Blue 2
150/36 Int. R. Blue 3 -----result
150/48 Int. C. Brown 4
150/48 R.Blue Int. 5
150/48 Int. C. Brown 6
Formular: i want to sum the total of R.Blue for 150/48 and 150/36
=SUMIF(C4:C9,"*"&150/36&"*"&"*""R.Blue"*"",D4:D9) but no result, but it should be 3
giving sumif function for more than one citeria on one cell of text.
Dear Svetlana,
thank you very much for posting useful information regarding use of SUMIFS function.
Basir
from Afghanistan
I use excel to export my collection data (med billing)and would like a formula to compare the cells to find matching account numbers, add their balances together (collection agency only takes accounts over $10)and give me a total in the cell of my choice. Some accounts range from 1 charge to multiple and it would be easier to look in one box instead of adding 10 lines to see if it's over(or under). I have looked at some of the formulas but nothing seems to fit.
Thank you
Светлана, я просто хочу вам сказать, что у вас прекрасный блог - лучшее из того, что я встречал в Интернет по теме Excel, и еще у вас прекрасный английский.
Спасибо большое! Мне очень приятно что наш блог читают и соотечественники :)
Svetlana,
First, thanks for the very long post, and for the many responses to comments. I am a little stuck on one of the formulas you provided, in "How to use SUMIFS in Excel - formula examples," specifically, in example two, where you use the TODAY formula. I tried to tweak it like this:
=SUMIFS(E3:E1000,D3:D1000,"="&MONTH(A2),D3:D1000,"="&YEAR(A2))
This is a sample of what my data looks like (column C is empty, and column B is where I'm pasting my formula):
A, B, D, E
Month-End Date, Month-End Total Fees, Date Fee Assessed, Amount of Fee
11/30/2014, [formula - should equal $9.00], 11/15/2014, $5.00
12/31/2014, [formula - should equal $1.00], 11/21/2014, $4.00
01/31/2015, [formula - should equal $0.00], 12/03/2014, $1.00
02/28/2015, [formula - should equal $2.00], 02/05/2015, $2.00
In column A, each month will only show up once, but in column D, it might show up many times, or never.
I am simply getting zero in all cells in column B.
Thank you for any help you can give.
Robert
I figured something out. Thought I would share it here:
=SUMPRODUCT(--(YEAR(D$3:D$10000)=YEAR(A3)),--(MONTH(D$3:D$10000)=MONTH(A3)),(G$3:G$10000))
I know it's not a SUMIF function, but in case anyone was heading down the wrong path, like I was, it might help. And no, I don't know how it works. I adapted it from #6 in this thread: https://www.mrexcel.com/forum/excel-questions/613222-use-formula-sumifs-criteria-range.html, using trial and error. Sorry if this is not kosher, to post a link to another site.
Robert
What will be the formula in above example,
If I want a sum of Qty for criteria ;
Product = Apple and/or Product = Banana
Supplier = John
Hi Pradeep,
In this case, you can add up 2 SUMIFS functions:
=SUMIFS(C2:C9, A2:A9, "apple", B2:B9, "John") + SUMIFS(C2:C9, A2:A9, "banana", B2:B9, "John")
Thanks Svetlana for quick reply,
So does that mean we can give 2 criteria for single column in a single function ?
Pradeep,
In this particular example, we add up 2 different SUMIFS functions because you want a formula to work with the OR logic.
Yes, you can supply 2 criteria for a single column in a single function, but they will work with the AND logic. For example, the following formula sums values in A2:A11 that are greater than 5 AND less than 10:
=SUMIFS(A2:A11, A2:A11, ">5", A2:A11, "<10")
I tried using the formula as show in Example 3, sumproducts & sumifs, but the formula is giving me the sum of everything except what I asked it to sum. Also, it doesn't sum the data on the top row. I'm very puzzled!
Please help.
Thank You,
Hi Richard,
It'd difficult to detect the source of the problem without seeing your data. If you can send your sample workbook with the formula to our support team at support@ablebits.com, we will try to help.
SKU QTY.
LM200 156
LM100 84
LM25 57
LM10 4
EG200 14
EG100 17
EG25 10
EG10 3
DJ200 41
DJ100 2
DJ25 0
CLA200 19
CLA100 98
CLA25 32
EBF200 41
EBF100 36
EBF25 33
ELC25 2
GT200 232
GT100 125
GT25 63
GT10 1
GEg25 42
GJas25 90
GM25 62
GM10 1
GTL100 61
GTL25 56
GTL10 1
LG25 6
GLH25 21
DIG25 0
OMC25 0
SM25 0
CAM25 0
PPM25 0
LR25 0
TinDG100g 0
TinDR100g 0
TinAS100g 0
RITUAL 0
09CB100 0
12CB100 0
Hi.
my question is how can sum value against if name is duplicate in a column.pls suggest.
just to add in above comment that the sum needs to be done only for rows where the value of column A is A/C.
Hi Sufi,
Here you go:
=IF(A1="A/C", C1-B1, "")
A B C
Leave Type Start Date End Date
A/C 22-Jul-15 24-Jul-15
A/C 01-Aug-15 10-Aug-15
I want to get a total sum(in days) of difference of C-B for each row. How can i achieve that?
What would the formula be for a SUMIF (Column A is the code) (Column B is the Date) (Column C is the Amount) What would the formula be for the amount of a certain code within a date range, say (July)?
hi, i want to count the planned qty against till date. i have used formula sumif(date column, date cell ref, qty column). unable to get the results. where as if i try a numerical no in place of cell ref, this works. can anyone helps.
B C
Jul-15 Plan
1 0
2 0
3 0
4 180
5
6 0
7 180 8 date
8 180 540
9 0 0
SUMIF(B4:B12,"<=8",C4:C12) - this works
SUMIF(B4:B11,"<=M10",C4:C11) - this does not work
yes I also have the same problem If I give cell reference it does NOT work instead if I give the cell content it works! Any solution please?
example if A1 has a numeral 123 and if i put criteria as >A1 it does NOT work but if I redo as >123 it works!
Ganesh,
Make sure you enter the cell reference like ">"&A1
Hi Reddy,
In SUMIF arguments, the correct syntax for cell references is as follows:
SUMIF(B4:B11,"<="&M10,C4:C11)
Your Sumifs explanation is great! I have a question you may have covered but I didn't find it:
As in your example of fruit, I need a formula that determines the quantity(C) based on the name(B)and product(A). In other words a multi array sumifs. I've tried just separating the sumifs sections with commas(,)but that doesn't work.
Thanks for any help you can give me.
Ann
Hi Ann,
The formula from the "fruit" example should work for your task as well, e.g:
If your Excel shows a message like "We found a problem with this formula...", most likely the List Separator is set to semicolon rather than a comma in your Regional settings. In this case, separate the SUMIFS' arguments with semicolons, for example:
=SUMIFS(C2:C9; A2:A9; "product"; B2:B9, "name")
To check this, you can star typing SUMIFS in the formula bar and Excel will display the correct syntax for you. You can also check out the List separator in the Control Panel > Region and Language > Additional Settings.
I have list of activities and their date of completion. From that coloumn of completion date, I wanted to know the no of activities completed by Today, Tomorrow , This Week and Next Week. I have used this formula"SUM(IF(EDC>=(TODAY()+7-WEEKDAY(TODAY(),2)+1),IF(EDC<(TODAY()+7-WEEKDAY(TODAY(),2)+1+7),1,0),0))" for Next Week "sum". Here, EDC = Expected date of completion.
=SUMIF(A1:A6,{"abc","efg"},C1:C6)<10
this formula not working
i want to change background color if sum of cells following the criteria =SUMIF(A1:A6,{"abc","efg"},C1:C6) is less than 10
Hello Burhan,
You cannot use arrays in SUMIF's arguments. Instead you can add up the results returned by 2 SUMIF functions:
=SUMIF(A1:A6,"abc",C1:C6) + SUMIF(A1:A6, "efg",C1:C6)
And then create a conditional formatting rule with the following formula:
=$D$1<10
Where D1 is the cell containing the SUMIF()+SUMIF() formula.
Hello sir,
I have faced sum problem to finding Pdb file. actully i have 100000 pdf file but i m looking 500 pdf any Procdure to finding Pdf file. I m always open one by one file this procedure is too long any sort cut to find all 500 file is simple ways .
Hi,
how can my formula automatically adjust to varying column changes,
e.g
= sumifs(sheet!A:A,sheet!B:B,"apple",Sheet!c:c,"Green")
what if the data received kept the same column heading but the columns changed
e.g - after data has changed
= sumifs(sheet!B:B,sheet!C:C,"apple",Sheet!D:D,"Green")
how can I link the formula to the column headings rather than an actual fixed column.
hope this made sense
Hi, stuck and need help.
I have an example of arriving on 4/3 and departing 6/1 which equals a total of 59 nights. I have a date range I want to SUM a value from an arrival of 5/31 and 6/1 so I need a solution to return the 1 in this example as my SUMIFS is summing the column because 59 is in the column. Need help to sum only 1 because of the date range I entered 5/31 to 6/1 when data is 4/3-6/1.
Arrival Departure Nights
3-Apr-15 1-Jun-15 59
Hi,
in a sum(sumif()) with any array, can i point array values to excel cells ?
I WANT TO SOME THAT COLLUM WHOES COLLUM I GAVE STATES YES ONLY IN MY EXCEL SHEETS
PLS HELP ME
I'm curious how to take a variety of Values and see how many combinations of these values I can create of 6 while remaining under or equal a total of $50,000.
How can I perform this in Excel? So lets say I have 55 variables all with different values say ranging from $6,000 to $12,000 and I want to see how many combinations of 6 I can have while remaining under or equal to the value of $50,000.
Fantastic answers - thanks!!
I'm trying to use the "sumifs" formula with multiple criteria and I just can't seen to get it to work correctly.
I have a summary tab in which I want to pull certain data by month, and I also have a tab called "Payments" where I want to just load payments for the whole year rather than having a separate tab for each month.
I want to grab the total of payments which are in column F
Column G shows the payment method (Check and ACH) and I only want ACH payments
Column D shows the payment date and in the formula below I am looking for only February payments
So, my criteria is: Total of ACH payments in February.
Here is my formula:
=SUMIFS(Payments!$F:$F,Payments!$G:$G,"ACH",Payments!$D:$D,">=2/1/2015",Payments!$D:$D,"<=2/28/2015")
Can anyone help me figure out why it isn't working? I have tried to add the apostrophe's around payments to indicate the tab but they keep disappearing...ie: 'Payments!'
Hi Erika,
Your formula works just fine on my test sheet. How exactly isn't it working on your side?
Dear Sir/Madam,
I'm now working in a hydropower devcelopment project. due i'm weak in excel. I would like to sum or count the number of issue(s) occured/opened and corrective action done/completed in each month (monthly basic)(i.e. number of issues opened in Jan, Feb and Mar... & Number of issue closed/completed in Jan, Feb and Mar...)
I tried many ways with SUMIF, SUMPRODUCT and COUNTIF. But i cannot get the resultant.
Looking to hear from you
Best
Svetlana Thank you for your help I have done my financial report !! It's really helpful please keep it go on.
Thank you, it is really helpfull.
Thanx again..
Dear,
the quires is 17562463 how add ":" after two number exp:-17:56:62:46:3
Svetlana Cheusheva, thank you so much for this helpful article!
Wow, this is what I am looking for!
The information on this article is very helpful! (really a BIG help for me)
Thank you very much ablebits.com!!
=SUMIFS($C$2:$C$3168,$A$2:$A$3168,">=90000", $A$2:$A$3168, ">=93599", $B$2:$B$3168,J2)
Any ideas as to why this formula is not working?
C = my totals
A = Zip Code Range
">=90000" - a zip code
B = a state code
Any ideas would be greatly appreciated!
Hi Suzanne,
In your formula, you specify 2 conditions for the same range in column A (">=90000" and ">=93599"). The first one is redundant or probably you meant something different?
=SUMIFS($D$2:$D$3168,$A$2:$A$3168,">=90000", $A$2:$A$3168, ">=93599", $B$2:$B$3168,J2)
Shipto_Zip Sales_Tax_State 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
00603 PR - 1,469.00 (1,469.00) - -
00674 PR - 203.00 - - 203.00
00680 PR - 1,293.00 - - 1,293.00
00705 PR - - 384.00 - 384.00
00725 PR 2,617.40 2,331.90 771.90 72.00 5,793.20
00784 PR - - - 805.00 805.00
Hi there
I would like to sum column D, providing that certain criteria are met. One of my criteria reference values in a range of cells.
Here is my formula, the answer need to be 40
Sales in the west =SUM(SUMIFS(D1:D5,A1:A5,"sales",B1:B5,"west",C1:C5,A13:A20))
A B C D
1 sales west mark 20
2 purchace west albert 10
3 sales north john 50
4 sales south shane 30
5 sales west Dina 20
13 albert
14 charles
15 catherine
16 Dina
17 henry
18 jason
19 john
20 mark
Hi Alfred,
Try this one:
=SUMIFS(D1:D5, A1:A5, "sales", B1:B5, "west")
Dear Sir,
I would like to sum the total hours capacity in different date. What's the formula provided?
TTL HRS DATE
3.46 4/13/2015
0.97 4/13/2015
1.03 4/13/2015
0.10 4/14/2015
1.39 4/14/2015
0.94 4/14/2015
3.42 4/15/2015
1.17 4/15/2015
1.06 4/16/2015