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. Continue reading
Comments on: How to use Excel SUMIFS and SUMIF with multiple criteria
by Svetlana Cheusheva, updated on
Comments page 9. Total comments: 675
Good day
If you could please advise.
I am trying to do a sumifs using three criteria, but the formula only allows me to add two criteria for evaluation?
Is there a version of excel that allows this, or is there a combination formula that I could use?
Thanking you in advance.
Thank you! Much appreciated help.
{=sum((F:F="")*(A:A<20)*(B:B))}
F = Name , A = Value , B = Value
I use this formula when I want to know the value which is under 20). This is fine. All I wanna know is how can I calculate the value which between 20 to 50. Please help me out.
Svetlana Cheusheva, you are fantastic. This was quite a piece of valuable information. Thanks a lot, you made my day.
I have a person information spreadsheet with about 2000 rows. I need a formula that will total the 'Sex' and 'Race' columns for the gender and race details. Ex. White Males = ?
Black Males = ? White Females = ?. The single criteria in the 'Sex' column will be either M or F. The 'Race' criteria will be a single identifier for the race. This seems to be a common problem but I cannot make it work. Thanks for any help.
Hello, David:
With that many rows of data I would use a Pivot Table.
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
ActiveCell.FormulaR1C1 = "=RC[-1]-SUMIF(R9C14:R90C14, RC[-3], R9C11:R90C11)" THIS WORKS FINE
But - I need it to look as to columns
i.e.
RC[-1] is the total hours a crew is available
R9C11:R90C11 is a column containing the hours required for each job
I need the hours from Column 11 to subtract from the total crew hours available if:
Column 14 OR Column 15 matches the crew name in column rc[-3]
THIS IS NOT WORKING
ActiveCell.FormulaR1C1 = "=RC[-1]-SUMIFS(R9C11:R90C11, R9C14:R90C14, RC[-3], R9C15:R90C15, RC[-3])"
if cell A has a value then cell k = CELL D - CELL A
If cell B has a value then cell k = CELL D + CELL B
some one please help me with a formula for the above.
Hi, Darsan,
if your value is in A2, and you need to check whether it contains a number, put the following into K cell where you want to see the result:
=IF(ISNUMBER(A2),D2-A2,"")
in case you need to see if there's anything at all in the cell, use the following:
=IF(NOT(ISBLANK(A2)),D2-A2,"")
Please adjust the formula for your second condition as well.
Feel free to check out our article about the IF function to learn why it is used and when in Excel.
Hello,
I have a similar formula, but needs to look at two cells to see if they have values or not. basically I want to do this in AZ9:
IF(isblank(R9) and isblank(AP9),"",sum(AP9,-(R9))
If both R9 and AP9 are blank (they both included formulas) then AZ9 returns blank(""). If one of them has a number i need to take AP9 minus R9.
Column I (Bridge #)
Column M ( Grade)
Column P (Qty)
Actually, I need
=SUMIF($I$5:$I$2276,"B1",$P$5:$P$2276)
need to use 3 column 2 criteria
Hi.
In your original example, if one was wanting to find out how many apples were not sold by Pete, is there a way to do this without telling the formula to calculate Mike and John and Sally's contributions?
I.E. minus Pete (and any other multiples you want to find out).
I have a spreadsheet where I need to calculate nationalities and I want to exclude only certain nationalities for my total. Is there a way to do this without having to input all of the nationalities I want into the formula?
The SUMIFS with array formula works really well for the nationalities I now need to exclude from my final cells.
Thanks
Hi Dan,
You can use the "not equal to" operator, like this:
=SUMIF(B2:B10, "<>"&"Pete", C2:C10)
To exclude more than 1 item:
=SUMIFS(C2:C10, B2:B10,"<>"&"Pete", B2:B10,"<>"&"Mike")
Hi Svetlana,
Thanks a lot!
If there a way to use array of items? e.g. sum all cells which does not contain Mike, Pete, Anna, Sam.
Hi Svetlana
I forgot to say thanks for this formula. It completely did the trick.
Thanks
Dan
Hi,
I was trying to do multiple if statements in one cell, when I try to enter the third if&sumif it states that I've entered too many arguments. Do you have any work around suggestions. Essentially I want the value in the cell to change based on what the user selects in the drop down box and the column that is calculated as the sum varies upon their selection.
=IF($H$8="PO Amount",SUMIFS('01-2017'!AA:AA,'01-2017'!L:L,[@[More Info]],'01-2017'!C:C,[@[PO No.]]),IF($H$8="Open PO",SUMIFS('01-2017'!AG:AG,'01-2017'!L:L,[@[More Info]],'01-2017'!C:C,[@[PO No.]])))
Thank you
Hi,
The below function code will perfectly work in same sheet, i required multiple pages code, could you help on this.
=SUMIFS(D3:D274,B3:B274,"Niyaz",C3:C274,"yes")
Hi,
please take a look at this tutorial to learn how to reference other sheets of your workbook. Lots of examples are included :)
I am trying to create a formula in a column with the range (f3:f27). I would like to count how many times, numbers between 0 and -150 are in that range? What should my formula be? =sumif(f3:f27,"-150") is not working. What am I missing?
Hello,
I'm trying to sumifs from a dropdown list with this codes 3.1, 3.2 ... when it comes to 3.10 it takes the sum of 3.1 and not 3.10.
=SUMIFS(H13:H265,F13:F265,"3.10")
Can someone help??
Many thanks,
Gurutze
I am trying making the standard format to use the time sheet for technician,From this i need to calculate 2 separate time for all technician based on number of calls per day,one for Administration Time which is time used for Travelling in-between the customer & office and other one for time used at customer place,how much time Technician spent for that particular customer.See below format i used for my sheet & formula for the same.
Format:
Office Time In (A) /In time @ Customer-1 Place(B) / Out time @ customer-1 Place(C) / In time @ Customer-2 Place(D) / Out time @ customer-2 Place(E) /In time @ Customer-3 Place(F) / Out time @ customer-3 Place(G).
Formula : =((B16-A16)+(D16-C16)+(F16-E16))
If i used above formula to calculate the Admin time,i am getting Minus Value if i not fill the time data@cell or if i use zero value for customer-3.
Pls advice.
Thanks & Regards
Dillibabu
When creating a SUMIF statement you search via a row?
You search columns when using SUMIF/SUMIFS. If your data is in Rows, copy and paste transposed to move the data from rows to columns.
too good
thank you so much
hi, tnx & can you plz. help me to get the formula for below..
col:A Col:B Col:C Col:D
product supplier qty date
banna jony 10 1-Feb
cherry moni 5 2-Jan
apple toni 15 1-Feb
pineaple koni 10 1-Mar
banna jony 10 1-Feb
banna jony 10 1-Jan
cherry moni 5 2-Jan
apple toni 15 1-Feb
pineaple koni 10 1-Mar
supplier:jony
product:appels
date :1-Feb
result: 20
plz. Sugest the formula (sumifs) to get the result: 20 , supplier=jony, product=appels, date=1-feb,
Waiting for you kind help.
B/R
Masud
=SUMIFS($C:$C,$B:$B,"jony",$A:$A,"apple",$d:$d,42767)
This is assuming you are looking at 2/1/2017. If not, enter the date you're looking for in Column D into an open cell, change the format to number, and replace the 42767 from the formula with the updated number.
Correction; damn dyslexia. Jony sold no apples on 2/1, according to the data. He did, however sell 20 banna on that date.
Hi Trenton,
Thank you for sharing your knowledge on our blog!
Just a minor improvement on the formula that can save a few seconds on date conversion:
Excel SUMIF and SUMIFS can understand dates in the default format, so you can include the date directly in the formula like this:
=SUMIFS($C:$C,$B:$B,"jony",$A:$A,"apple",$D:$D,"02/01/2017")
Or you can supply it by using the DATE function:
=SUMIFS($C:$C,$B:$B,"jony",$A:$A,"apple",$D:$D,DATE(2017,2,1))
However, this will not return a result of 20. According to your data, Jony sold only 10 apples on 2/1.
Hello,
I need a formula that sums two numbers (cell a4+a5) if it is greater than 1 and less than 35. Also, I need the same for the difference of the same cells and criteria with absolute value. Lastly, for each formula where the sum or difference is outside the range, I need the cell to blank (no zeroes).
Thank you for your assistance,
Tim
Hi
I currently have a formula as =SUMIF(B3,"Buy",G3)*I3+J3+K3
(If buy, multiply units by unit price, PLUS fee, PLUS tax).
How do I add another argument, so that if B3 is “Divided”, “Distribution” , or “Sell”, then multiply units by unit price, LESS fee, LESS tax)?
Many thanks
Hammad
Hello Ablebits team!
I have an interesting one for you. Can we make the sum_range dependent upon criteria in another cell? Let's say I have this formula:
=SUMIFS(Base!$AF$4:$AF$2762,Base!$O$4:$O$2762,$B9)
But I want to be able to change the $AF based on a value in another cell (Let's assume cell D6), so that when I update the value in D6 to AG, the cell begins summing information in Column AG instead of Column AF. Is this possible with a formula, or do I have to update the sum_range columns every time I update?
Большое спасибо!
Hi,
This is Darshan.
In example one. Can we mix with criteria rang also, as below.
> Original >> SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],...)
> Amended >> SUMIFS(sum_range, criteria_range1, criteria from A1:A20,[criteria_range2, criteria2],...)
I hope this explain.
Thank you,
Darshan
if in criteria i used less than sign like <60 and if i want to get output from range who is matching with <60 then how i use sumifs formula to get that
please find below example for more details on it,
Age Amount
<60 2500.00
i m using formula as =sumifs(Range:criteria,criteria) so what is wrong in it
Hi,
I have a situation similar to "Example 2. SUM & SUMIF with an array argument" under "Using Excel SUMIF with multiple OR criteria".
But, the array is specified in a different cell i.e. the criteria {"John","Mike","Pete"} is given in a different cell. How do I reference that cell with criteria in the sumif statement.
Thanks.
Hi,
I want to include "or" condition in sumifs in two different columns. How can that be done?
Ex: Col1 Group : "Primary","Seconday" and Col2 Subjects : "Maths", "Science", "History".
Name Group Subject Marks
Ashna Primary English 50
Ashna Primary Maths 70
Ashna Primary Science 45
Ashna Primary History 55
Ashna Secondary English 75
Ashna Secondary Maths 68
Ashna Secondary Science 89
Ashna Secondary History 98
Kirti University Science 56
Kirti University Spain 63
Kirti University History 52
Kirti Secondary Science 87
Kirti Secondary History 36
Kirti Secondary Maths 49
Archana Secondary History 126
Mansi Primary Maths 89
Sarika Secondary Science 90
Out of this, for every name, marks should be added where group is Primary / Secondary and Subject Maths / Science /History
Hai,
I want to total of three sum range using below formula in sumif function.Please suggest. I do not want to use three formula of Sumif+Sumif+Sumif.
=SUMIF($D$7:$D$18,D4,$E$7:$G$18)
where D7:D18 is range , D4 is criteria and E7:G18 is sum range.
Hi,
I want to use the SUMIFS function for the forllowing table
Sheet1
Column A has name of Customers Column B, C & D has year 2014, 2015 & 2016
Sheet 2
Column A Customer Name
Column B Year
Column C Months
Column D Revenue from Customer - Product A
Column E Revenue from Customer - Product B
Column F Revenue from Customer - Product C
Column G Revenue from Customer - Product D
Column H Revenue from Customer - Product E
I prepared the table in sheet 1 as mentioned above, but with sumifs i can create only one table for revenue from customer - product A
If I want to change the table showing result as 'Revenue from Customer Product - B' I need to change he the details of sum range in sumifs formula.
Can you give me a formula or a logic with whcih I can just change the reference formula get changed as per the reference in that cell.
Thanks in Advance.
Anand J
THANKS!!
Hello I wants formula for
If I wants to sum B2 shell with minimum number shell in the range of A1:A6
Please assist me
i have a few conditions, before i want my calculations
if Column A equals Cell A1 and if Column D equals Cell D1, then calculate Column E
But my formula gives me #Value
Forumula used and a couple of other that doesn't want to work
=SUMIF(A:A,A1 & sumif D:D,D1,G:G) ive tried and tried
Please please help
Hi, Erica,
try using SUMIFS function, that allows multiple criteria. For the requirements you described the formula will be:
=SUMIFS(E:E,A:A,A1,D:D,D1)