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

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

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

  3. Hi sir,

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

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

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

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

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

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

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

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

  11. Thank you

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

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

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

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

  16. When creating a SUMIF statement you search via a row?

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

  17. too good
    thank you so much

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

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

      1. Correction; damn dyslexia. Jony sold no apples on 2/1, according to the data. He did, however sell 20 banna on that date.

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

      2. However, this will not return a result of 20. According to your data, Jony sold only 10 apples on 2/1.

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

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

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

    Большое спасибо!

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

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

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

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

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

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

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

  28. THANKS!!

  29. Hello I wants formula for
    If I wants to sum B2 shell with minimum number shell in the range of A1:A6

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

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

  31. Hello i have a question.

    I was looking for a function that add votes or number based on criteria. for example in range from A1:A10 for every "Artour" will add +1 to box C3, so if i have 1 Artour C3=1, and if i have 5 it will be 5. I was thinking perhaps sumif was the right function but apparently i was wrong. I could really use some help.

    thanks before

    1. Hi, Artour,
      the function you need is called COUNTIF. For your given example, place the following into C3 cell:
      =COUNTIF(A1:A10,"Artour")

      Note, that it won’t count the word “Artour” within a cell with more than one word (meaning, it works only for the cell with one single word “Artour” in it). For more details and info, please, feel free to check another article out - COUNTIF in Excel.

  32. I am having trouble with text. My formula is =SUMIFS(YTD!$H:$H,YTD!$E:$E,">=1/1/2017",YTD!$E:$E,"<=1/31/2017",YTD!$G:$G,"Jason")
    I want to know how many deals Jason did in January, but it's only picking up some of them, and not every time his name comes up. Any tips?

  33. in sumif formula =SUMIF(ZDDR!$G$5:$G$1456,A5,ZDDR!$H$5:$H$1456)
    it automatically calculates what is in A5 matches with the ranges

    but when i use sumifs formula

    =SUMIFS(ZDDR!$H$5:$H$1456,ZDDR!$B$5:$B$1456,"=B4",ZDDR!$G$5:$G$1456,"=A5")

    it will not give any answer. My problem is i want to use cell number insted of text how is it possible.

  34. Hi Svetlana, could i please get some help with this one

    My previous formula is working, but we have had a change of data from another department, so i am hoping to get an outcome from the following

    Column AF has a total figure of $, in that total, they are either amounts of $135 or $180, but the spreadsheet that i am importing into my spreadsheet doesn't specify that amount that the total is derived from.

    my previous formula was

    =SUM(SUMIFS(AF11:AF5000,I11:I5000,{"BSB30115"},AK11:AK5000,{"organisation name"}))

    I would like two formulas as they need to go into two separate cells, so from Column AF, i need a formula that uses that column and works out the total amount, whether it is 2 amounts of $360 (these amounts can be as high as $2520 in total), 1 amount of $180 and then the other one being the same type of formula but the amount of $135.

    so the return values would be in two cells, one being for the $180 amounts (total) and then one for $135 amounts (total)

    so my end result would be splitting the one column to have two different totals for each of the criteria, hope this makes sense,

    Thank you in advance

  35. Thank you very much for the tutorial. It really helped me.

  36. the formula that I am using is not showing up in the previous 2 posts, when I try copy and paste the formula. SUMIFS($Q$2:$Q$3208, $F$2:$F$3208, $R20, $G$2:$G$3208, ">=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

  37. Hello,
    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    Thank you,
    jimmy

  38. Hello,
    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    I need to sum

  39. Dear Experts,

    Kindly help below mentioned query...use sumif function - account code is unique data, but i need sum if month should be matching ....

    Month AccountCode Amount
    Aug-16 21011077 4290
    Sep-16 21011077 4070
    Oct-16 21011077 5500
    Nov-16 21011077 5500
    Dec-16 21011077 4730
    Jan-17 21011077 5230
    Feb-17 21011077 5990

    A.Code Description Aug-16 Sep-16
    21011077 Visa Letter Fee

    Unique Data is Account code, but sum is arrive based on month.

    Thanks in Advance...
    Ahmed Khan

    1. Dear Ahmed,

      You can try this formula.

      =SUM(SUMIFS(amount_range,account_code_range,"21011077",month_range,{"Aug-16","sep-16"}))

      Hope this helps.

  40. I need a formula for a profit and loss sheet I am creating. I need to add a column of cells but only those that are listed as a specific month ie 'January', 'Feb' etc.
    Then I need these January amounts to be bought back into another sheet and for them to match in the corresponding cell.

    Example Sheet 1 'Expenses Entry'-
    Column heading in Column B'Month'- January/Feb/ April/ Dec etc
    Column heading in Column D 'Description'- Bank Charges/ Food/ Tools etc
    Column Heading in Column F 'Amount'- $40.00/ $55.00 etc ( these are the amounts I need to be added up if they all fall under January, then I wil copy the formula for Feb/ March/ April etc

    Sheet 2- 'Profit and Loss
    Column A is for all the descriptions- Bank Charges/Food/Tools etc
    Column C is where I want the sum of all January's Bank Charges to be or whatever the name of the 'Description' is

  41. In "Example 2. SUM & SUMIF with an array argument", there is the following combination of functions

    =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

    Instead of writing "John","Mike",etc., can we use the cells that they are into? For example,

    =SUM(SUMIF(C2:C9, {G3,G2,G4} , D2:D9))

    even though the above it's wrong

  42. Can someone help in solving this as this is killing my machine. Some simple formula to run sheet faster

    =SUMIFS('Same Day TAT'!$Q:$Q,'Same Day TAT'!$H:$H,Summary!$D9,'Same Day TAT'!$C:$C,Summary!$B$2,'Same Day TAT'!$D:$D,Summary!$B$3,'Same Day TAT'!$E:$E,Summary!$B$4,'Same Day TAT'!$F:$F,Summary!$B$5,'Same Day TAT'!$G:$G,Summary!$B$6,'Same Day TAT'!$L:$L,Summary!F$8)

  43. Sir
    I have two sheets , in one sheet there is only one column enlisted certain items and in second sheet there are two columns. In one column there is list of item & in second column, there is list of values corresponding to items in first column.
    Sheet 1
    Part No
    B
    A
    C
    D
    Sheet 2
    Engine Qty
    A 10
    B 10
    C 20
    D 20
    E 25
    A 15

    I want to run sum if in sheet two only for common items between first column in sheet 1 & sheet 2
    please guide

  44. Respected Sir,

    i want to create some formula for my office use but i am unable so will you please help me?

    if C25= 6,00,000
    then E29 can calculate 1% of C25 till 350000
    Thne E30 can calulate 15% of C25 till +100000
    and last E31 calculate 25 % of C25 balance amount (150000)

  45. i need to devolop sum if for tow condition

    sales incentive preapartin
    eligilbe only if sales (1) and collection shoulbe achived

  46. Awesome and fantastic concepts by the owner who imparted basic and advance concepts lots of thanks !!!!!!!!!!

  47. invoice num doc date amount
    20000056 24.01.2017 536
    20000057 23.01.2017 765
    20000058 24.01.2017 897
    20000059 23.01.2017 545
    20000060 24.01.2017 7654
    20000061 23.01.2017 897
    20000062 24.01.2017 987
    20000063 23.01.2017 651
    20000064 12.12.2016 210
    20000065 01.10.2016 610
    20000066 01.10.2016 211
    20000067 23.01.2016 109
    20000068 23.01.2017 187
    94000011 23.01.2016 145
    94000012 23.01.2017 167
    94000013 23.01.2016 198
    94000014 23.01.2016 103
    94000017 23.01.2016 1765
    94000019 23.01.2016 543
    94000032 23.01.2016 532
    94000033 23.01.2016 346
    94000034 23.01.2016 678

    I juat want to know that which all invoices I should add to get exact 3000

  48. I am having trouble coming up with a proper formula for this:
    This is a list of positions open and when it was filled.
    Site Status Created Filled
    A FT 1/1/2000 1/2/2000
    B FT 1/2/2000 2/1/2000
    C P 1/3/2000
    A P 1/4/2000
    B FT 1/5/2000 2/1/2000
    C P 1/6/2000
    A FT 1/7/2000 1/4/2000
    B P 1/8/2000
    C P 1/9/2000
    I am trying to write a formula that basically asks, If site = x, then sum 8 for FT and 6 for PT, for each entry if my date in question falls between the created and filled dates. Also if the filled date is blank that means it's still open. So that needs to be included.
    My gut tells me it should be an index/match formula (or vlookup) which i've done in the past.
    This is the forumla that i have so far =SUMIFS(Hours,Site, "="&B3, Status, "="&D3, Created, ""&C3)
    I created a new column that listed the status as a number.

  49. Name Amount 1099 Y/N
    A (250) N
    A (2,546.58) Y
    B 184.50 N
    C (3,500.00) Y
    B (1,500.00) N

    Hi, if I want to sum if 1099 column indicates "Y" by "Name" so that it will populate A with "Y" ...B with "Y" ..so forth. how do i formulate the functions?

  50. I'm trying to avoid sumifs + sumifs + ...
    I have multiple criteria in my data table (date, location, type, subtype) that must match. I'd like to be able to have location and type look to a list for criteria rather than hard code, as I need to use similar function 15 or more times and prefer to change the list reference than typing in and hard-coding the names, places as they change.

    sumifs(C:C,B:B,list1,A:A,datecell,D:D,sitelist)
    Above is oversimplified as I reference 4-5 criteria ranges

    it works when I reference 1 list and it seems that either I have a bad list reference of it won't allow criteria to be lists in 2 places.

    can you help?

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 :)