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.
672 comments
QUESTION
Rubber Material Qty1 Cushion Gum Qty2
LL215-128 11.5 CUS GUM 225 MM-EB 1.3
LL215-128 11 CUS GUM 200 MM 1.3
60-62-18-P-Mtk 2.5 CUS GUM 200 MM 1.3
NH165-103 3/4 6.6 CUS GUM 165 MM 0.6
FR175-119 6.8 CUS GUM 165 MM 0.7
NH165-103 3/4 6.6 CUS GUM 165 MM 0.7
FR175-119 6.8 CUS GUM 165 MM 0.8
NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
FR175-119 6.8 CUS GUM 165 MM 0.7
NH165-103 3/4 6.8 CUS GUM 165 MM 0.6
NH165-103 3/4 6.7 CUS GUM 165 MM 0.6
LG-215-128-VT 10.2 CUS GUM 225 MM-EB 1.5
LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.4
LG-215-128-VT 10.1 CUS GUM 225 MM-EB 1.5
SM 220-128 12.4 CUS GUM 225 MM-EB 1.4
SM 240-130 12.4 CUS GUM 225 MM-EB 1.5
SM 230-130 12.7 CUS GUM 225 MM-EB 1.5
SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
SM 220-130 12.4 CUS GUM 225 MM-EB 1.4
SM 230-130 12.8 CUS GUM 215 MM-EB 1.4
SM 230-130 12.7 CUS GUM 225 MM-EB 1.4
SM 230-130 12.7 CUS GUM 215 MM-EB 1.5
Rl 210-127 11.4 CUS GUM 200 MM 1.4
Rl 210-127 11.4 CUS GUM 200 MM 1.5
RESULT: I WANT TO KNOW SUM OF MATERIAL USED WITH HELP OF SUM-IF / OTHERWISE ANY OTHER FORMULA IS THERE.
MATERIAL
60-62-18-P-Mtk
CUS GUM 165 MM
CUS GUM 200 MM
CUS GUM 215 MM-EB
CUS GUM 225 MM-EB
FR175-119
LG-215-128-VT
LL215-128
NH165-103 3/4
Rl 210-127
SM 220-128
SM 220-130
SM 230-130
SM 240-130
i need sumifs function -- my company loaded flour every day 14 trucks for different companeis . so i need each company howmany ton per day? so please help me
Hello,
I am doing a multiple criteria SUMIFS but with one range I'd like it to be an OR. (The works great for multiple if there are only one or two NOTS, but there are several 'not wanted' and only 2 wanted.)
I can do a SUMIFS + SUMIFS but the braces option do not seem to work for a cell reference being used for the criteria:
working:
=SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$28 ) + SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,"="&DropDowns!$A$27 )
but want to do something like this (since all other criteria is the same)
... $H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal"
============================================================
=SUMIFS($K$4:$K$503,$H$4:$H$503,"=" & $AA45,$F$4:$F$503,"Renewal",$L$4:$L$503,{"="&DropDowns!$A$28,"="&DropDowns!$A$27} )
is it placement? Does the 'or' criteria need to be first? (the Or function does not work here)
$L$4:$L$503,{"="&DropDowns!$A$28,"="&DropDowns!$A$27}
Is there another sequence for the braces? {"="&DropDowns!$A$28,"="&DropDowns!$A$27}
The weird "="&cell is the only way my system will allow the criteria in that cell to be used maybe since it is a text.
Thank you for any insight.
excel formula for 25 mg*2=50 mg
I would like to sum the Expected Payment column if the date in the Payment Due Date column is a date in 2018. I have tried both sumif and sumproduct, however, I believe that since the columns I am referencing are formulas which generate a $, date, or blank, it is not working.
K13:K36=Expected Payment (is a formula that generates a $ or blank) L13:L36=Payment Due Date (is a formula that generates a date of blank)
If someone has an answer for this, it would be greatly appreciated! :)
Hi Svetlana Cheusheva,
Hope you are doing good.....
I have couple of question on Sum formula.
Sum it to total
Sumif is to total with one condition/Criteria
Sumifs is to total with one or more condition/criteria that to if all condition/criteria satisfy only then only it display the results that is output. my question it should give the output or result even if any one condition/criteria satisfy also.
Thanks in advance
Mahaboob
Hello, please can someone help me?
I have 2 sheets, one with the current sales by location and the other sheet exactly the same but without figures. I basically need to model it for different scenarios.
This is how it works in my head but not in reality.
=IF(Scenarios!B1="1",SUMIF('Project Sales month only'!C:C,Scenarios!C3,'Project Sales month only'!I:I*2))
I have 3 different scenarios so will need to add the other 2 if statements on but please can someone help me?
Thanks in advance
Hi, I am trying to SUMIF the following:
Channel, Product and Price
Under channel column I have different criteria: agent, google, organic, etc.
Under product: 5 different products/criteria
Under price: products actual prices that I want to get it summed accordingly.
i.e.: Channel range. I want to sum all google leads for the various products.
for Google sales, MBA products only, Google sales, Postgraduate Certificates, etc.
This is what I am doing but it is not summing correctly.
=SUMIFS(J2:J40,$B$2:$B$40, "google",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40,"facebook",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40,"organic",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "affiliate",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "alumni",$F$2:$F$40,"PGC")+SUMIFS(J2:J40,$B$2:$B$40, "referral",$F$2:$F$40,"PGC")
In the end I want to find out how much sales we got from google for a particular product.
Can you help, please?
thanks,
Guiherme
Guiherme, Would it work if you put the absolute$ reference on the J2:J40 AND put a space before and after each of the + symbols ?
was >>>SUMIFS(J2:J40,$B$2:$B$40, "google",$F$2:$F$40,"PGC")+SUMIFS(J2:J40...
try>>> SUMIFS($J$2:$J$40,$B$2:$B$40,"google",$F$2:$F$40,"PGC") + SUMIFS($J$2:$J$40...
a space before and after the + symbol may help the formula work consistently.
Hope something works out for you.
Ruth
Thanks for give me the strong understanding and useful knowledge
1.5hrs+0.3hrs=?hrs how to calculate time in this form
Hello,
If I understand your task correctly, please try the following formula:
=TEXT((1.5+0.3)/24,"hh:mm")
Hope this will help.
I've found help on ablebits.com with my Excel questions on a number of occasions, and I believe it's important to leave feedback. Thank you for providing clear instructions with images and examples on a variety of specific questions I had. I'm sure I'll be coming back again soon.
Am glad to find help here.
I have cells from A-H containing grades. E.g: A=9, B=3, C=2, D=7, E=3, F=5, G=1, H=2.
How can I use excel to sum only the best six grades out of the eight grades?
E.g:(1+2+2+3+3+5)=16
So that total best six grades will be 16.
Thanks.
Hello,
If I understand your task correctly, please try the following formula:
=SUMIF(A1:H1,"<"&6)
Hope this will help you!
I have a questions
I have many categories and want to sum the categories by the months. how can i do to calculate it?
Thanks
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Thanks so much for all your info!!!
great help!
From Verious month i need YTD sum upto selected month.
select Mar MTD YTD
Jan 200 500 1000
Feb 300
Mar 500
Apr 300
send me the formula for YTD 1000 get;while MTD is Mar
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
how can i sum of 2 cell if above cell has "if" formulas.
for ex. if c2 sum has if formula
and c3 has to if formula
how can i put total of above 2 in c4 cell?
Hello, could someone please help me to get a formula when I need to sum up values in one column based on criteria in another column not equal to "John". In other words, I want to sum all the values for Mike, Pete but not John.
Thank you!
Hi sir,
Supposes that we have some emp. id I want sum of start with 40 no. How can we do this.
Hi Natalia,
Hope your well.
I'm trying to sum up Quantities based on date range.
In my workbook, I have the following worksheets
“Stock”
“CustomerPN”
Within “Stock” worksheet I have two cells with dates, $B$2 start date, $C$2 End Date, and part number in cell B21 for example
Within “Customer” worksheet I have named ranges
“OrderQTY”
“CustomerPN”
“DateDue”
This formula, with multiple criteria’s works perfectly
=IF($B$2=$C$2,SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$B$2),(SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$B$2)+SUMIFS(OrderQTY, CustomerPN,B21,DateDue,$C$2)))
ie if start date in B2 equals end date in C2, then give result for B2 date only, else give result based on dates between B2 and C2
Note “B21” is my lookup value
however I’m guessing it can be written shorter.
I’ve tried
=SUMIFS(OrderQTY, CustomerPN,B21,DateDue,”> =”&$B$2, DateDue,”< =”&$C$2) but the result returned is zero
Can you see what I’m doing wrong, or is the longer version of the formula best?
Regards
Eddie
Sorry Svetlana, for addressing you as Natalia, who helped me on previous enquiry from her blog
Hahaha... it seems both Svetlana and Natalia are responding to various people in this blog, so sorry for my confusion ladies, my respect to both of you, when you get a chance and if either one of you can reply I would appreciate much
Hi.
I need help writting a formula that adds the top 4 values out of 5.
I think it is a sumproduct and a large formula but I can't seem to get it to work.
=SUMPRODUCT(LARGE(B6:f6,{1,2,3,4})) is not working for me?
Any ideas?
Many thanks.
Christer