Comments on: How to use Excel SUMIFS and SUMIF with multiple criteria

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 page 8. Total comments: 675

  1. Lot of thanks to to you. I got the perfect formula at the right time. It saves me lots of time. Great.

  2. =SUMIFS(H2:H1000, D2:D1000,"vehari", G2:G1000, "short")

    I want to calculate a range...when two conditions are ture..

    But this formula is not working.

    Kindly help.

  3. How can I get a count of the number of rows returned using the sumifs function?
    My current function is
    =SUMIFS $B$2:$B$105,$C$2:$C$105,O111,$D$2:$D$105,O115,$E$2:$E$105,O113)
    and I would like to know how many rows were returned to get my total that was returned with the sumifs function.
    Thanks,
    Matt

  4. This tutorial was a massive help!
    Easy to understand and follow, and it works!:)
    Thanks so much for providing this for the public!

  5. Hi,I need SUMIFS formula with multiple columns reference.column1-type, column2-length adress, column3- lenghcount. now need total count value with each length adress(column2)

  6. Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can someone please help

  7. Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can some please help

  8. Sorry people, read cell A2 as follows

    =sumif(criteria range,"Apple",sum range)

  9. Hey people can you tell us how to use sumif for the following;

    Criteria 1 - Apple
    Criteria 2 -Orange
    Criteria 3 -Lemon
    Criteria 4 -Pineapple

    we want to use sum if to total the sum of "Apples" And all others in s single cell.

    eg:

    In Cell A1
    =sumif(criteria range,"Apple",sum range)

    In Cell A2
    =sumif(criteria range,"Apple",sum range)

    this above formula is not working??? to be specific it sometimes work

    also tell us can we apply this formula across sheets in the same excel work book?

    awaiting your wise response....

  10. I am trying to make work easier for my team by building a formula that adds up data based on two criteria: The month (this is okay). Each month has for to five weeks. I want the formula to add data in each months for all weeks in the month but up to the current week. I did the formula below. It however appears that Excel does not recognise the last argument below which uses reference to a cell:

    =SUMIFS('Weekly targets'!$F$14:$BE$14,'Weekly targets'!$F$1:$BE$1,'Summary by Product'!$E$3,'Weekly targets'!$F$3:$BE$3,"<=$C$4")

  11. Hi,
    I need help to put this note in this formula.

    =IF(AT1350=400000,0,IF(AT1350<=800000,1000,IF(AT1350<=1200000,2000,IF(AT1350<=2400000,(AT1350-1200000)*5%,IF(AT13504800000,(AT1350-4800000)*15%+(300000)))))))

    Note: { Provided that where the taxable income exceeds Rs. 800,000/- the Minimum tax payable shall be Rs. 2,000/-

  12. Hello everyone,

    Getting to grips with SUM and SUMIF and have been working on a particular problem using:

    =SUM(IF(K10="",O10-P10),IF(K10="PGTA3",O10-(P10/2)))

    For this means if "PGTA3" is shown in cell K10 then divide P10 by 2 and subtract the result from O10. However, in the cell K10 I would like this to not be limited to "PGTA3" but anything that includes "PGTA" or "GRP" within the cell.

    Essentially O10 is a room size, P10 is a number of students but if ""PGTA or "GRP" is in cell K10 that means there is a second group in another row and the number of students present in the room would be divided in half. Cell K10 could say "PGTA12, "PGTA2", "PGTA3", "GRP1", "GRP2" or a combination of PGTA fields (like "PGTA1,PGTA1" or something like ",PGTA1". The presence of a comma is of no interest to me; if a comma were in a cell alone (it could happen) I want that ignored so I don't think a pure wildcard would work (ie if ANY text in cell K10, divide P10 in half). "PGTA*" and "*" and "*PGTA*" doesn't seem to work anyway and doesn't allow for "GRP". Any help very gratefully received! I aced indexing and array formulas this morning so I'm sure I can ace this too. :)

  13. =SUMIFS('Priority & Standard Cost'!$AJ:$AJ,'Priority & Standard Cost'!$B:$B,Sheet3!B3,'Priority & Standard Cost'!$M:$M,">=04-01-2017",'Priority & Standard Cost'!$M:$M,"='Contract Details'!E3",'Priority & Standard Cost'!$M:$M,"<='Contract Details'!F3")
    What is the difference in both formulas.
    I have just changed the date from cell in which date is mentioned in same format....but result is 0....Why?

  14. This is my formula:
    =SUMIF($C$10:$C$30,"*" & $C$7 &"*",D10:D30)

    C7=1 just one number, referring to a shift. So it could be 2 or 3.
    D Column = Are just values

    Any suggestions.

  15. Last formula solved my problem, Tons of thanks.

  16. What expression do I need to use, to do the following for +100 different criteria, that counts up all quantities in the different criteria's.

    =SUM(SUMIF(B2:B152, {"Malus"}, D2:D152))
    =SUM(SUMIF(B2:B152, {"Pyrus"}, D2:D152))

    to display on the worksheet
    Malus 25
    Pyrus 36
    etc etc for +100 different criteria

    Its looking more like I am going to have to use VB code

  17. one more time:

    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)

  18. I am having problems with my SUMIFS. I need to bring the Invoice Amount from two different worksheets over to my main sheet, but I only want them if they match my GL Code and my Monthly Date.

    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “=07/01/2018”, “<=07/31/2018”)

    -or-

    =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date], MonthlyExpensesSummary[July])
    +SUMIFS(Other[Check Amount], Other[G/L Code], MonthlyExpensesSummary[@[G/L Code]], Other[InvoiceDate],[July])

    But, neither one work. The top one I can at least get in without error, but doesn't give me results.

    Can you help??

    Thanks for any assistance,

    Tracey

    1. That first one has a typo - it is actually like this:

      =SUMIFS(ItemizedExpenses[Check Amount], ItemizedExpenses[G/L Code], MonthlyExpensesSummary[@[G/L Code]], ItemizedExpenses[Invoice Date],">=07/01/2018”, “= 07/01/2018”, “<=07/31/2018”)

  19. Hello,
    I am doing multiple calculation and cannot make correct combination of formulas for the following example:

    Column1 Column2
    4 x
    4 x
    5
    6 x
    6 x
    7
    7
    7

    I need formula that will conunt criteria from first column without repetition and only for fow that has mark "X" in second column.

    For count of first column I used following formula =SUM(IF(FREQUENCY(...), but I cannot cmbinate it with IF second column has "X".
    Thanks in advance
    Savo

  20. How to calculate Total sales of Google and Costco customer in North East region???

    Date Region Sales Rep Customer Product COGS Sales
    6/16/2006 NorthEast Jon Google QJV Item $1,835.90 $3,165.34
    6/15/2006 NorthEast Jon Amazon.com ZET Item $6,971.50 $9,295.33
    6/15/2006 SouthEast Chin Amazon.com ZON Item $5,406.27 $9,163.17
    6/15/2006 SouthEast Chin Solar and Wind Inc. JFA Item $736.25 $836.65
    6/14/2006 NorthEast Jeri Costco ZET Item $2,065.11 $4,589.14
    6/14/2006 NorthEast Troung Sherman Williams LZB Item $2,553.28 $3,754.82
    6/14/2006 NorthEast Steven Peet's Coffee EJL Item $2,315.43 $3,405.05

  21. 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.

  22. Thank you! Much appreciated help.

  23. {=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.

  24. Svetlana Cheusheva, you are fantastic. This was quite a piece of valuable information. Thanks a lot, you made my day.

  25. 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.

    1. Hello, David:
      With that many rows of data I would use a Pivot Table.

  26. 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

  27. 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

  28. 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.

  29. excel formula for 25 mg*2=50 mg

  30. 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! :)

  31. 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

  32. 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

  33. 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

    1. 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

  34. Thanks for give me the strong understanding and useful knowledge

  35. 1.5hrs+0.3hrs=?hrs how to calculate time in this form

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =TEXT((1.5+0.3)/24,"hh:mm")

      Hope this will help.

  36. 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.

  37. 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.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =SUMIF(A1:H1,"<"&6)

      Hope this will help you!

  38. 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

    1. 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.

  39. Thanks so much for all your info!!!
    great help!

  40. 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

    1. 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.

  41. 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?

  42. 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!

  43. Hi sir,

    Supposes that we have some emp. id I want sum of start with 40 no. How can we do this.

  44. 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

    1. Sorry Svetlana, for addressing you as Natalia, who helped me on previous enquiry from her blog

      1. 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

  45. 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

  46. 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])"

  47. 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.

    1. 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.

      1. 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.

  48. 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

  49. 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

    1. 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")

      1. 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.

      2. Hi Svetlana

        I forgot to say thanks for this formula. It completely did the trick.

        Thanks

        Dan

  50. 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.]])))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)