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

  1. Svetlana - this is fantastic. I bought Ablebits a while ago for several of the functions and I love this additional help!

    I've been wanting to convert an investment tracking sheet from simple formulas that are time consuming to reproduce each cycle to advanced/array formulas that will do the heavy lifting each time once I give it the new data.

    As such - this information, especially the SUM and SUMIF Array Argument, helped a great deal.

    However, when I reproduced the array but changed SUM to AVERAGE and SUMIF to AVERAGEIF, it does not produce the correct average result. I know this as I worked it out the old way to make sure. I couldn't find a separate entry on AVERAGEIF/AVERAGEIFS, so I thought I'd ask here!

    1. It also doesn't like it if I do COUNT/COUNTIF, too.

      -Bret

      1. Hi Bret,

        Thank you for choosing Ablebits add-ins! As for AVERAGEIF/AVERAGEIFS, you read my mind - I'm working on this tutorial at the moment :)

        If you can send us (support@ablebits.com) your sample workbook along with the expected result and we will try to figure out a proper formula.

        1. Thank you, Svetlana! I just sent that to you.

        2. Hi can you help me out with one of the figures
          I have perticular set of alphabet numbers but each number carrying different colours , i want sort the colours with the diff numbers and required count

  2. I would like to give sumif function for more than two citeria on the same cell

    Example
    150/48 R.Blue Int.
    150/48 Int. T. Blue
    150/36 Int. R. Blue
    150/48 Int. C. Brown
    150/48 R.Blue Int.
    150/48 Int. C. Brown

    Formular: i want to sum the total of R.Blue for 150/48 and 150/36

    =IF(D7="","",SUMIF(Table,D7&"*",kgs)+SUMIF(Table,E7&"*",kgs)+SUMIF(Table,F7&"*",kgs)+SUMIF(Table,C7&"*",kgs))

    giving sumif function for more than one citeria on one cell of text.

    Regards

    1. I would like to give sumif function for more than two citeria on the same cell

      Example
      150/48 R.Blue Int. 1
      150/48 Int. T. Blue 2
      150/36 Int. R. Blue 3 -----result
      150/48 Int. C. Brown 4
      150/48 R.Blue Int. 5
      150/48 Int. C. Brown 6

      Formular: i want to sum the total of R.Blue for 150/48 and 150/36

      =SUMIF(C4:C9,"*"&150/36&"*"&"*""R.Blue"*"",D4:D9) but no result, but it should be 3

      giving sumif function for more than one citeria on one cell of text.

  3. Dear Svetlana,
    thank you very much for posting useful information regarding use of SUMIFS function.

    Basir
    from Afghanistan

  4. I use excel to export my collection data (med billing)and would like a formula to compare the cells to find matching account numbers, add their balances together (collection agency only takes accounts over $10)and give me a total in the cell of my choice. Some accounts range from 1 charge to multiple and it would be easier to look in one box instead of adding 10 lines to see if it's over(or under). I have looked at some of the formulas but nothing seems to fit.
    Thank you

  5. Светлана, я просто хочу вам сказать, что у вас прекрасный блог - лучшее из того, что я встречал в Интернет по теме Excel, и еще у вас прекрасный английский.

    1. Спасибо большое! Мне очень приятно что наш блог читают и соотечественники :)

  6. Svetlana,

    First, thanks for the very long post, and for the many responses to comments. I am a little stuck on one of the formulas you provided, in "How to use SUMIFS in Excel - formula examples," specifically, in example two, where you use the TODAY formula. I tried to tweak it like this:

    =SUMIFS(E3:E1000,D3:D1000,"="&MONTH(A2),D3:D1000,"="&YEAR(A2))

    This is a sample of what my data looks like (column C is empty, and column B is where I'm pasting my formula):

    A, B, D, E
    Month-End Date, Month-End Total Fees, Date Fee Assessed, Amount of Fee
    11/30/2014, [formula - should equal $9.00], 11/15/2014, $5.00
    12/31/2014, [formula - should equal $1.00], 11/21/2014, $4.00
    01/31/2015, [formula - should equal $0.00], 12/03/2014, $1.00
    02/28/2015, [formula - should equal $2.00], 02/05/2015, $2.00

    In column A, each month will only show up once, but in column D, it might show up many times, or never.

    I am simply getting zero in all cells in column B.

    Thank you for any help you can give.

    Robert

    1. I figured something out. Thought I would share it here:

      =SUMPRODUCT(--(YEAR(D$3:D$10000)=YEAR(A3)),--(MONTH(D$3:D$10000)=MONTH(A3)),(G$3:G$10000))

      I know it's not a SUMIF function, but in case anyone was heading down the wrong path, like I was, it might help. And no, I don't know how it works. I adapted it from #6 in this thread: https://www.mrexcel.com/forum/excel-questions/613222-use-formula-sumifs-criteria-range.html, using trial and error. Sorry if this is not kosher, to post a link to another site.

      Robert

  7. What will be the formula in above example,

    If I want a sum of Qty for criteria ;

    Product = Apple and/or Product = Banana
    Supplier = John

    1. Hi Pradeep,

      In this case, you can add up 2 SUMIFS functions:
      =SUMIFS(C2:C9, A2:A9, "apple", B2:B9, "John") + SUMIFS(C2:C9, A2:A9, "banana", B2:B9, "John")

      1. Thanks Svetlana for quick reply,
        So does that mean we can give 2 criteria for single column in a single function ?

        1. Pradeep,

          In this particular example, we add up 2 different SUMIFS functions because you want a formula to work with the OR logic.

          Yes, you can supply 2 criteria for a single column in a single function, but they will work with the AND logic. For example, the following formula sums values in A2:A11 that are greater than 5 AND less than 10:
          =SUMIFS(A2:A11, A2:A11, ">5", A2:A11, "<10")

  8. I tried using the formula as show in Example 3, sumproducts & sumifs, but the formula is giving me the sum of everything except what I asked it to sum. Also, it doesn't sum the data on the top row. I'm very puzzled!

    Please help.

    Thank You,

    1. Hi Richard,

      It'd difficult to detect the source of the problem without seeing your data. If you can send your sample workbook with the formula to our support team at support@ablebits.com, we will try to help.

  9. SKU QTY.
    LM200 156
    LM100 84
    LM25 57
    LM10 4
    EG200 14
    EG100 17
    EG25 10
    EG10 3
    DJ200 41
    DJ100 2
    DJ25 0
    CLA200 19
    CLA100 98
    CLA25 32
    EBF200 41
    EBF100 36
    EBF25 33
    ELC25 2
    GT200 232
    GT100 125
    GT25 63
    GT10 1
    GEg25 42
    GJas25 90
    GM25 62
    GM10 1
    GTL100 61
    GTL25 56
    GTL10 1
    LG25 6
    GLH25 21
    DIG25 0
    OMC25 0
    SM25 0
    CAM25 0
    PPM25 0
    LR25 0
    TinDG100g 0
    TinDR100g 0
    TinAS100g 0
    RITUAL 0
    09CB100 0
    12CB100 0

  10. Hi.
    my question is how can sum value against if name is duplicate in a column.pls suggest.

  11. just to add in above comment that the sum needs to be done only for rows where the value of column A is A/C.

    1. Hi Sufi,

      Here you go:
      =IF(A1="A/C", C1-B1, "")

  12. A B C
    Leave Type Start Date End Date
    A/C 22-Jul-15 24-Jul-15
    A/C 01-Aug-15 10-Aug-15

    I want to get a total sum(in days) of difference of C-B for each row. How can i achieve that?

  13. What would the formula be for a SUMIF (Column A is the code) (Column B is the Date) (Column C is the Amount) What would the formula be for the amount of a certain code within a date range, say (July)?

  14. hi, i want to count the planned qty against till date. i have used formula sumif(date column, date cell ref, qty column). unable to get the results. where as if i try a numerical no in place of cell ref, this works. can anyone helps.
    B C
    Jul-15 Plan
    1 0
    2 0
    3 0
    4 180
    5
    6 0
    7 180 8 date
    8 180 540
    9 0 0

    SUMIF(B4:B12,"<=8",C4:C12) - this works
    SUMIF(B4:B11,"<=M10",C4:C11) - this does not work

    1. Hi Reddy,

      In SUMIF arguments, the correct syntax for cell references is as follows:

      SUMIF(B4:B11,"<="&M10,C4:C11)

    2. yes I also have the same problem If I give cell reference it does NOT work instead if I give the cell content it works! Any solution please?
      example if A1 has a numeral 123 and if i put criteria as >A1 it does NOT work but if I redo as >123 it works!

      1. Ganesh,

        Make sure you enter the cell reference like ">"&A1

  15. Your Sumifs explanation is great! I have a question you may have covered but I didn't find it:
    As in your example of fruit, I need a formula that determines the quantity(C) based on the name(B)and product(A). In other words a multi array sumifs. I've tried just separating the sumifs sections with commas(,)but that doesn't work.
    Thanks for any help you can give me.

    Ann

    1. Hi Ann,

      The formula from the "fruit" example should work for your task as well, e.g:

      If your Excel shows a message like "We found a problem with this formula...", most likely the List Separator is set to semicolon rather than a comma in your Regional settings. In this case, separate the SUMIFS' arguments with semicolons, for example:
      =SUMIFS(C2:C9; A2:A9; "product"; B2:B9, "name")

      To check this, you can star typing SUMIFS in the formula bar and Excel will display the correct syntax for you. You can also check out the List separator in the Control Panel > Region and Language > Additional Settings.

  16. I have list of activities and their date of completion. From that coloumn of completion date, I wanted to know the no of activities completed by Today, Tomorrow , This Week and Next Week. I have used this formula"SUM(IF(EDC>=(TODAY()+7-WEEKDAY(TODAY(),2)+1),IF(EDC<(TODAY()+7-WEEKDAY(TODAY(),2)+1+7),1,0),0))" for Next Week "sum". Here, EDC = Expected date of completion.

  17. =SUMIF(A1:A6,{"abc","efg"},C1:C6)<10

    this formula not working

    i want to change background color if sum of cells following the criteria =SUMIF(A1:A6,{"abc","efg"},C1:C6) is less than 10

    1. Hello Burhan,

      You cannot use arrays in SUMIF's arguments. Instead you can add up the results returned by 2 SUMIF functions:
      =SUMIF(A1:A6,"abc",C1:C6) + SUMIF(A1:A6, "efg",C1:C6)

      And then create a conditional formatting rule with the following formula:
      =$D$1<10

      Where D1 is the cell containing the SUMIF()+SUMIF() formula.

  18. Hello sir,

    I have faced sum problem to finding Pdb file. actully i have 100000 pdf file but i m looking 500 pdf any Procdure to finding Pdf file. I m always open one by one file this procedure is too long any sort cut to find all 500 file is simple ways .

  19. Hi,

    how can my formula automatically adjust to varying column changes,
    e.g
    = sumifs(sheet!A:A,sheet!B:B,"apple",Sheet!c:c,"Green")

    what if the data received kept the same column heading but the columns changed
    e.g - after data has changed

    = sumifs(sheet!B:B,sheet!C:C,"apple",Sheet!D:D,"Green")

    how can I link the formula to the column headings rather than an actual fixed column.

    hope this made sense

  20. Hi, stuck and need help.

    I have an example of arriving on 4/3 and departing 6/1 which equals a total of 59 nights. I have a date range I want to SUM a value from an arrival of 5/31 and 6/1 so I need a solution to return the 1 in this example as my SUMIFS is summing the column because 59 is in the column. Need help to sum only 1 because of the date range I entered 5/31 to 6/1 when data is 4/3-6/1.

    Arrival Departure Nights
    3-Apr-15 1-Jun-15 59

  21. Hi,
    in a sum(sumif()) with any array, can i point array values to excel cells ?

  22. I WANT TO SOME THAT COLLUM WHOES COLLUM I GAVE STATES YES ONLY IN MY EXCEL SHEETS

    PLS HELP ME

  23. I'm curious how to take a variety of Values and see how many combinations of these values I can create of 6 while remaining under or equal a total of $50,000.

    How can I perform this in Excel? So lets say I have 55 variables all with different values say ranging from $6,000 to $12,000 and I want to see how many combinations of 6 I can have while remaining under or equal to the value of $50,000.

  24. Fantastic answers - thanks!!

  25. I'm trying to use the "sumifs" formula with multiple criteria and I just can't seen to get it to work correctly.
    I have a summary tab in which I want to pull certain data by month, and I also have a tab called "Payments" where I want to just load payments for the whole year rather than having a separate tab for each month.

    I want to grab the total of payments which are in column F
    Column G shows the payment method (Check and ACH) and I only want ACH payments
    Column D shows the payment date and in the formula below I am looking for only February payments
    So, my criteria is: Total of ACH payments in February.

    Here is my formula:
    =SUMIFS(Payments!$F:$F,Payments!$G:$G,"ACH",Payments!$D:$D,">=2/1/2015",Payments!$D:$D,"<=2/28/2015")

    Can anyone help me figure out why it isn't working? I have tried to add the apostrophe's around payments to indicate the tab but they keep disappearing...ie: 'Payments!'

    1. Hi Erika,

      Your formula works just fine on my test sheet. How exactly isn't it working on your side?

  26. Dear Sir/Madam,

    I'm now working in a hydropower devcelopment project. due i'm weak in excel. I would like to sum or count the number of issue(s) occured/opened and corrective action done/completed in each month (monthly basic)(i.e. number of issues opened in Jan, Feb and Mar... & Number of issue closed/completed in Jan, Feb and Mar...)
    I tried many ways with SUMIF, SUMPRODUCT and COUNTIF. But i cannot get the resultant.

    Looking to hear from you

    Best

  27. Svetlana Thank you for your help I have done my financial report !! It's really helpful please keep it go on.

  28. Thank you, it is really helpfull.
    Thanx again..

  29. Dear,

    the quires is 17562463 how add ":" after two number exp:-17:56:62:46:3

  30. Svetlana Cheusheva, thank you so much for this helpful article!

  31. Wow, this is what I am looking for!
    The information on this article is very helpful! (really a BIG help for me)
    Thank you very much ablebits.com!!

  32. =SUMIFS($C$2:$C$3168,$A$2:$A$3168,">=90000", $A$2:$A$3168, ">=93599", $B$2:$B$3168,J2)

    Any ideas as to why this formula is not working?

    C = my totals

    A = Zip Code Range

    ">=90000" - a zip code

    B = a state code

    Any ideas would be greatly appreciated!

    1. Hi Suzanne,

      In your formula, you specify 2 conditions for the same range in column A (">=90000" and ">=93599"). The first one is redundant or probably you meant something different?

  33. =SUMIFS($D$2:$D$3168,$A$2:$A$3168,">=90000", $A$2:$A$3168, ">=93599", $B$2:$B$3168,J2)

    Shipto_Zip Sales_Tax_State 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
    00603 PR - 1,469.00 (1,469.00) - -
    00674 PR - 203.00 - - 203.00
    00680 PR - 1,293.00 - - 1,293.00
    00705 PR - - 384.00 - 384.00
    00725 PR 2,617.40 2,331.90 771.90 72.00 5,793.20
    00784 PR - - - 805.00 805.00

  34. Hi there
    I would like to sum column D, providing that certain criteria are met. One of my criteria reference values in a range of cells.
    Here is my formula, the answer need to be 40
    Sales in the west =SUM(SUMIFS(D1:D5,A1:A5,"sales",B1:B5,"west",C1:C5,A13:A20))

    A B C D
    1 sales west mark 20
    2 purchace west albert 10
    3 sales north john 50
    4 sales south shane 30
    5 sales west Dina 20

    13 albert
    14 charles
    15 catherine
    16 Dina
    17 henry
    18 jason
    19 john
    20 mark

    1. Hi Alfred,

      Try this one:

      =SUMIFS(D1:D5, A1:A5, "sales", B1:B5, "west")

  35. Dear Sir,

    I would like to sum the total hours capacity in different date. What's the formula provided?

    TTL HRS DATE
    3.46 4/13/2015
    0.97 4/13/2015
    1.03 4/13/2015
    0.10 4/14/2015
    1.39 4/14/2015
    0.94 4/14/2015
    3.42 4/15/2015
    1.17 4/15/2015
    1.06 4/16/2015

  36. I am trying to take overtime and regular hours out of one column and put into two columns.
    i have all hours column that adds all the days total hours i would like to have my regular hour column take out everything up to 40 hours and then the OT column take everything over 40 hours. is that possible.

    1. I'm interested in the same thing. Did you ever get a response?

      1. Assuming the hours are in column A, you can use the following formulas to extract:

        - Regular hours: =IF(A2<=40, A2, "")
        - Overtime: =IF(A2>40, A2, "")

        The screenshot below shows the result:

        Extract regular and overtime hours

        If you are looking for something different, please clarify.

  37. Hi,

    Resource Usage
    Bob 50%
    Mike 25%
    Bob 75%

    I would like to turn all Usage values for Bob RED when the sum exceed 100%. I know it is a SUMIF condition, but the syntax is alluding me. Any help would be appreciated.

  38. Hello everyone,

    I´m not sure which function to use to sum the products below in a different table.I tried to use the "sumif" function without sucess.

    Products amounts Aples ?????
    Aples 1500.00 Oranges ?????
    oranges 400.00 Potatoes ?????
    potatoes 590.00 Mango ?????
    mango 522.00
    oranges 789.00
    Bananas 632.00
    Aples 878.00
    potatoes 965.00

    Can anyone help me?

    1. Hi Carlos,

      The SUMIF function is the right choice. Supposing that column A is the list of products, B is Amounts and C is the product names in the second table (beginning in C2), you can use the following formula:

      =SUMIF($A$2:$A$10, C2, $B$2:$B$10)

      1. Hi Svetlana,

        Im trying to apply same criteria for different columns (non adjacent), they are working well individually. Error occurs when I tried to add them together with the following formula.
        =
        SUMIFS(E18:E10000, G18:G10000, "John", I18:I10000, "01/07/2014”) +
        SUMIFS(E18:E10000, K18:K10000, "John", M18:M10000, "01/07/2014”) +
        SUMIFS(E18:E10000, O18:P10000, "John", Q18:Q10000, "01/07/2014”) +
        SUMIFS(E18:E10000, S18:G10000, "John", U18:U10000, "01/07/2014")

        Is there another way to add them without having to calculate individually (in different cells) and add. ?

        - Ben

        1. There was a typo in my earlier formula.

          =
          SUMIFS(E18:E10000, G18:G10000, "John", I18:I10000, "01/07/2014”) +
          SUMIFS(E18:E10000, K18:K10000, "John", M18:M10000, "01/07/2014”) +
          SUMIFS(E18:E10000, O18:O10000, "John", Q18:Q10000, "01/07/2014”) +
          SUMIFS(E18:E10000, S18:S10000, "John", U18:U10000, "01/07/2014")

  39. Hi can someone pls help me to count different criterias but in the same cell example if the followiing (math,bio,eng) (geo,math,) (bio,math,eng) in this case the bracket represent one cell but all those cells are in one column. My be 123 cells each having information of different subjects and now i want to count how many times each subject appears in that column. Remember the subjects are divided by commars in each cell. Pls i need to use this formular now friday if anyone can pls

  40. I have given data in columns A and B

    Column A column B

    05/04/15 1
    08/04/15 6
    07/04/15 0
    19/04/15 2

    Actually I need to calculate the number of days from todays's date I mean count down.
    but it must true 2 conditions for count down eg. whole range of column eg A2:A100 must be greater than todays date and whole range of column B eg. b2:b100 must be 1 then substract today's date with column A

  41. Anyone can help, so I have on same sheet 12 different data filled tables for 12 months, on 13th table I would like to see cumulative sum of desired data. I woould like to choose to sum between february and june for example. Table is generated like 5X4 for every month where you can input data how many fruits (apples, bannanas, pears...) I have in different storages (1,2,3...) and would like to know how many fruits I have had between february and june. Hope someone understands and can help. Tnx

  42. Excellent examples and explanations - congratulations on your expertise

  43. This was a very useful article and was just what I was after - thank you!

    Quick question, how can I use wildcards for the "Excel SUMIFS with multiple OR criteria" section please?

    For example, if column C (Supplier) had a surname as well in the same cell, yet we still wanted to continue searching for just for forename, how would we do this using the formula from "Example 3. SUMPRODUCT & SUMIFS". I tried the following but it didn't work:

    =SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, "*"&H1:H3&"*",0))), D2:D9)

    Thanks.

  44. Hello Svetlana,

    Your explanation vis a vis the use of the Sumifs function was clear, straightforward, and immeasurably useful. I've been attempting to devise a method by which to accomplish the task that the Sumifs function can fulfill for the better part of three days (an attempt which led off in some pretty instructive, but ultimately futile directions). You have my sincere thanks for putting together a tutorial that I've little doubt required a fair degree of time and effort (not to mention the time that went into becoming an expert in the material in the first place). I can move forward with my project thanks to your work. Once more, thank you.

  45. I WANT TO A FORMULA THAT HELP ME FOR CREATING BANK STATEMENT IN EXCEL SHEET .
    FOR EXAMPLE:-THAT FORMULA ADD CREDIT SIDE WITH BANK BALANCE AND SUBTRACT DEBIT SIDE WITH AVAILABLE BANK BALANCE AUTOMATICALLY.

  46. I need to count text within multiple tables from the start of the year to TODAY. The tables being each month and the text being vacation days "V" in each cell. I'm having trouble defining the date in the range. The each cell in the top row has the following format:
    =TEXT(WEEKDAY(DATE(CalendarYear,3,9),1),"aaa")
    The first column is the employees name.
    I found the SUMIF function for adding using the date criteria, and the COUNTIF function the count the text "V". I'm having trouble combining the two.
    Thanks

  47. Thank you so much for your wonderful, clear easy to follow tutorials :) I have learnt so much and am amazed at how quickly I have done that!!!

  48. How to automate the below process?
    Parameter 2

    Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10
    Sales 10 10 10 10 5 5 5 5 5 5
    WOC 20 20 20 15 10 10 10 10 10 5

    if I vary parameter=4, woc on Wk1=40(consider first weeks sum)
    Wk2=35
    Please help

  49. I want a formula that will add up all the numbers but only if it has a B. What would be the best formula for that? I would like to and the number and letter all in once cell so it would look something like this.
    7B
    6B
    1R

  50. I have created the following array formula which works fine to summ based on the multiple GL accounts. Now i want to add another criteria in to the formula to only sum based on months. My data is in a list from and it is for 12 months. The idea is that formula will look at the header for name month and than only sum relevant dollars in to the column.

    Col H = dollars, Col D = GL account, Col A = Month

    =SUM(SUMIFS('GL Data'!$H:$H,'GL Data'!$D:$D,{"400000","400550","430260","400500","403000","403010","403505","404500"}))

    Please help. Rashid

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