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

  1. Scenario:
    Given data are availment dates, transactions and amounts per transaction

    I wanted to summarize the total for each dates and I have managed to use the =SUMIFS formula.
    However, I wanted to drag down the formula for the rest of the transaction dates and failed have the correct total.

    What should I do with my formula?

    TIA

  2. How can I make this formula below work without having to type/copy/Paste "180/470/560100704-Grants from State Agen" in the formula. I tried to do it with a reference to the cell Directly to the Left "B4" with the exact text of "180/470/560100704-Grants from State Agen" but the formula didn't work. I built this formula but i am sort of a novice at this. There are hundreds of lines that have different codes and text so it is better if i can reference the cell "B4" to make the formula work. A different formula is OK but this is how i figured out how to do this.

    Essentially i have 3,000 lines of a general ledger that i want to summarize into there perspective monthly entries.

    L2 = 2022
    L3 = October

    =SUMIFS(DATA!$G$3:$G$6000,DATA!$C$3:$C$6000,"180/470/560100704-Grants from State Agen",DATA!$E$3:$E$6000,SUMMARY!L3,DATA!$A$3:$A$6000,SUMMARY!L2)

      1. Let me elaborate more. The Formula is Looking at Tab "DATA" at Column C ("C3:C6000") for the Info on B4 (B4 is matching C3:C6000) and Returning info form Column G. It is also checking for a match in L2 and L3 for info in the Data TAB for Column E and A.

        =SUMIFS(DATA!$G$3:$G$6000,
        DATA!$C$3:$C$6000,
        "180/470/560100704-Grants from State Agen",
        DATA!$E$3:$E$6000,
        L3,
        DATA!$A$3:$A$6000,
        L2)

        I want to Replace "180/470/560100704-Grants from State Agen" with a Reference to B4 which you showed me ">"B4. But when i do this instead of returning a summary of $2,734 it is giving me 73,456. This i assume is a summary of the entire column of G Rather than just column G in October of 2022.

        =SUMIFS(DATA!$G$3:$G$6000,
        DATA!$C$3:$C$6000,
        ">"$B4,
        DATA!$E$3:$E$6000,
        L3,
        DATA!$A$3:$A$6000,
        L2)

      2. Thank you that detail (">"B4,)worked. But. It is Suming the entire column it is referencing without considering the other "Ifs" Aka Year(L2) and Month(L3).

        1. To sum not the whole column, add conditions to the SUMIFS formula. You can find all the information you need in the article above. To compare not the whole date, but only the month or year, use the MONTH or YEAR functions.

  3. Hello.

    What I am looking for is means to SUM values based on their concomitant criteria not being part of values already summed up elsewhere...

    e.g.

    [A] [B]
    [1] Bob 100
    [2] Bob 200
    [3] Sue 350
    [4] Tom 220
    [5] Bill -130
    [6] Jane -240
    [7] Jane -87

    COUNTA for COL[A] is going to be 7
    SUM for COL[B] is going to 413

    The grand total needs to be calculated, but values under 0 need to be excluded elsewhere for writing out concatenated text values.

    It took forever to do this using SUM, COUNTA, COUNTIF functions and a separate equation as a name range. But that was so clunky... 😒😯

    My main problem is dealing with SUM for multiple COUNTIF statements.

    Thank you.

  4. Thank you for detailed blog. I am looking for a formula to calculate total qty product wise in column E and F. for example.

    Apples 410
    Bananans 600
    Cherries 530
    Oranges 400

    Can you please help?

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail.

  5. What if I need a 2nd column data pulled? I currently have this formula but need to add an additional data info from a range of columns (from Sanchez!B8:B7)
    =SUMIFS(Sanchez!C8:C67,Sanchez!A8:A67,"<="&Summary!M7)

    1. Hi!
      If I understand your task correctly, try the following formula:

      =SUMIFS(Sanchez!C8:C67,Sanchez!A8:A67,"<="&Summary!M7)+SUMIFS(Sanchez!B8:B67,Sanchez!A8:A67,"<="&Summary!M7)

      Please read the above article carefully.

  6. In the Example Example 3. SUMPRODUCT & SUMIFS with Mike, John and Pete in column H,
    how can you use a single sumproduct function or other function in cell I1 to get the total broken down by Mike, John and Pete?
    SUMIFS can do this, but SUMIFS does not allow other functions within the ranges or criteria.
    It would look like this, the cell with 120 has the single formula that spills down based upon the number of items in column H.
    H I
    Mike 120
    John 250
    Pete 290

  7. i need formula to sum a column in a table based on which are details in two coloumns were nill and i need to make sure there is another column must include a specific text.please help

  8. Hi,

    If there's any text in QTY cell, how i will SUMIF by criteria? i mean i need to ignore text's in QTY column and sum the values in QTY column with a criteria i have in another sheet.

    Thanks

  9. Hi, is it somehow possible to use different size range lengths in SUMIFS

    for example, i have values in the range a1:a10 and i want to sum these values based on two different criteria.
    criteria 1 range=b1
    criteria 1 = "city"
    criteria 2 range=c1
    criteria 2 = "yes"

    =sumifs(a1:a10,b1,"city",c1,"YES")
    this is only a dummy example, i have multiple sheets in which i have to use this kind of values.
    this can be done using the helper column(in every sheet i have to create a helper column), but i want it without a helper column. (maybe possible...)

    thanks

      1. thanks for replay
        any workaround for this problem,,, maybe any other formula for this??

  10. How do I use TRIM formula within SUMIFS formula to clear invisible spaces??

  11. Great place to learn excel

    I have a query

    let's say I have a list of 1000 ordered however, my previous inventory is divided into sets of 100 and each set of 100 units has a unique landing cost now I want to know how I can enter these costs in a formula

    example

    Column A is the list of 1000 order and each cell in the column is having either 1 or 2 written
    Column B needs to add landing cost which is unique for each set of 100 units so It means I have 10 different landing cost

    Please share a formula that can automate the process

  12. sum should equal 20. You should use the + operator

  13. Hello Sir,
    Is there any shortcut for this formula
    SUMIFS(Net_Delivery21,CODE21,A6)+SUMIFS(Net_Delivery21,CODE21,B6)+SUMIFS(Net_Delivery21,CODE21,C6)+SUMIFS(Net_Delivery21,CODE21,D6)+SUMIFS(Net_Delivery21,CODE21,E6)

    Thanks

    2. sorry I mean this one Sir,

      SUMIFS(Z2:Z500,A2:A500,A6)+SUMIFS(Z2:Z500,A2:A500,B6)+SUMIFS(Z2:Z500,A2:A500,C6)+SUMIFS(Z2:Z500,A2:A500,D6)+SUMIFS(Z2:Z500,A2:A500,E6)

      Thanks

  14. Hi!
    As your instruction, we have

    1. =SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
    2. =SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

    I try to find how I can replace "Pete" in formula 1 into G4 (as the value of G4 is Pete) , the formula would become

    =SUMPRODUCT(SUMIF(C2:C9, {"Mike","John",G4}, D2:D9))

    but it doesn't work, pls help me how fix it!! B/c I want to create an array from specific text and from value of cell

    Tks!!!

  15. How to SUM the Qty automatically in MS Office 2007 Excel if OD, THK & SPECN changed.

    DESCN OD THK SPECN Qty
    Pipe 60.30 5.54 SA 106 GR B 28.26
    Pipe 60.30 5.54 SA 106 GR B 24.6
    Pipe 88.90 5.49 SA 106 GR B 3
    Pipe 88.90 5.49 SA 106 GR B 46.632
    Pipe 88.90 7.62 SA 106 GR B 11.52
    Pipe 88.90 7.62 SA 106 GR B 2
    Pipe 88.90 7.62 SA 106 GR B 3
    Pipe 88.90 7.62 SA 106 GR B 3
    Pipe 88.90 7.62 SA 106 GR B 46.048
    Pipe 88.90 7.62 SA 106 GR B 38.6
    Pipe 88.90 11.13 SA 106 GR B 6.2
    Pipe 114.30 6.02 SA 106 GR B 1
    Pipe 114.30 8.56 SA 106 GR B 7.26
    Pipe 114.30 8.56 SA 106 GR B 3
    Pipe 114.30 8.56 SA 106 GR B 3
    Pipe 114.30 8.56 SA 106 GR B 182.848
    Pipe 114.30 8.56 SA 106 GR B 0.7
    Pipe 114.30 8.56 SA 106 GR B 0.7

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  16. Great work, explain everything very clear with simple example. Thanks alot.

  17. Thanks very much for this great tutorial! I'm trying to create a formula that is a bit beyond my basic skill set and hoping you could provide guidance. I'm trying to pull data from one sheet into another, with multiple parameters as follows:

    One one sheet ("Data 2") I have "$ revenue" in column O and "Campaign Name" in column P " -
    So it looks a bit like this:
    Column O. Column P
    $100. Campaign A
    $50. Campaign A
    $75. Campaign A
    $250. Campaign B
    etc.

    I'm trying to put that into a summary sheet, to sum up revenue by campaign - and match by Campaign Name on that summary sheet (column B).
    So it looks like this
    Col B: Campaign Name Revenue
    Campaign A. (formula here should = $225)
    Campaign B. etc.

    I came up with this: =SUMIF('Data 2'!O:O,B:B,'Data 2'!P:P)

    2. (hit send too soon!) - but that formula is giving me a $0 result for some reason.

      Also, to make things more complex, I'd like to also suppress certain sales stages (sheet Data 2, column Q) from being included. I've tried this, but also is not working (error message, so definitely wrong..):
      =SUMIF('Data 2'!O:O,B:B,'Data 2'!P:P,'Data 2'!Q:Q"Closed Nurture","Closed Won","Closed Lost")

      1. Hi!
        Please re-check the article above since it covers your task.
        If I understand correctly, the formula might look like this:

        =SUMIFS(‘Data '2′'!O:O,’Data '2′'!P:P,B1,’Data '2’'!Q:Q,”<>Closed Nurture”,’Data '2’'!Q:Q,”<>Closed Won”,’Data '2’'!Q:Q,”<>Closed Lost”)

        1. Thank you! That looks exactly right - but I'm still getting a "formula parse error" (?)

          1. Hi!
            I can't check the formula that contains unique references to your workbook worksheets. I have shown you how to modify your formula. Use SUMIFS function.

  18. HI I HAVE FORMULA AS BELOW,

    HOW TO sum up column I only when the value of Column I is less than zero?

    =SUMIFS('DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

      1. Thank you so much but how could I incorporate with my Formula?
        =SUMIFS('DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

        When I am typing as below error is coming
        =SUMIFS('DD Policy Due'!I:I,"<0",'DD Policy Due'!I:I,'DD Policy Due'!H:H,Consolidated!D3) * IF(G3="D",1,-1)

        1. Hi!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =SUMIFS(I:I,I:I," < 0",H:H,D3) * IF(G3="D",1,-1)

  19. Thank you very much for this very useful post on Excel formulas for multiple criteria requirement.

  20. Please give me some solution for below requested formula if it is right
    =SUMIFS(A2:E2,">="120000-10%)

  21. Hi Alexander,

    I am trying to figure out how to calculate Monthly recurring revenues when new customers coming in and others leave.

    Assume all customers remain only 4 months

    Month 1 2 3 4 5 6
    New monthly revenues 100 300 500 700 900 1100
    Monthly recurring revenues 100 400 900 1,600 2,500 ?????

    How do I make sure that in month 6 I do the following?

    Sum all New monthly revenues from months 1 through 6 EXCEPT months 1 and 2 because the time that has passed by is greater than 4 months?

    Thank you
    -

    1. Hello!
      If you need to use a formula in the summation condition, then use the SUMPRODUCT function to calculate the sum by multiple criteria:

      =SUMPRODUCT(--(A1:L1>(MONTH(TODAY())-4)),--(A1:L1<=(MONTH(TODAY()))),A2:L2)

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      1. Dear Alexander,

        1. You are my hero
        2. I am forever grateful
        3. Your help will make me look good in front of my colleagues!

        Thank you!
        Carlos

  22. Hello, Sir kindly guide me what's the best platform for advanced excel learning and free of cost.

  23. I used below formula and total amount = 70. Seems it only adding "Terry". I want to see the total amount of Terry, John and Pete. Am I missing something? Thanks.

    =SUM(SUMIFS(B2:B10,A2:A10,{"Terry", "John", "Pete"}))
    name amount
    Terry $10
    John $20
    Terry $30
    Pete $10
    John $30
    Lian $20
    George $10
    Pete $20
    Terry $30

    1. Hi!
      Unfortunately, I could not get the result 70. Your formula gives a result - 150. Try to enter your formula as an array formula. Enter via Ctrl + Shift + Enter, not just Enter.

  24. Hi

    May i request anyone, how can i use cell reference in sum if instead of typing manually.

    Example-: SUMIF('Jan22-29.3.22'!$AL:$AL,"*WTFL*",'Jan22-29.3.22'!$AQ:$AQ)

    Question: Why need to edit the formula to type WTFL or any other word in order to get total.

    Thanks
    Anoop

    1. Hello!
      Replace the value in the formula with a reference to the cell that contains that value.

      SUMIF(‘Jan22-29.3.22′!$AL:$AL,$A$1,’Jan22-29.3.22’!$AQ:$AQ)

      A1 -- *WTFL*

  25. i was using sumifs formula with multiple criterias. but i am unable to fetch the previous criteria data if some criteria are blank. need your support

  26. Trying calculate the total revenue (in cells T3:T11000) IF the service type = Social (in cells D3:D11000) AND the project status = Active (in cells K3:K11000) AND the invoice date falls in between 01/01/2021 and 01/31/2021. Report totals to appear on a separate tab from the summary data on tab entitled "All Projects".

    My (erroneous) attempt:
    =SUMIFS('All Projects '!T3:T11000,'All Projects '!D3:D11000,"=*SOCIAL*",'All Projects '!C3:C11000,">=01/01/2021",'All Projects '!C3:C11000,"<=01/31/2021")+SUMIFS('All Projects '!T3:T11000,'All Projects '!K3:K11000,"*active*")

    Can you please help? Thank You!

    1. Hi!
      Please re-check the article above since it covers your case.
      Here is a sample formula for you

      =SUMIFS(T3:T7,C3:C7,">01/01/2021",C3:C7,"<31/01/2021",D3:D7,"*Social*",K3:K7,"*Active*")

  27. Dear Sir, i want to apply this condition can you help me regarding this

    Excess [if (C7 = 1+1+1,3*D7*G7),(if (C7 = 1+1,2*D7*G7),if (C7 = 1,1*D7*G7),0

  28. Dear i want to apply this condition can you help me regarding this

    Excess Un-adjusted Credit [if F24 = Yes and F25 > F17 then (F25 - F17); otherwise zero; if F24 = No then (F8 - F25)]
    her "F" is column and numbers are rows

  29. Hi, I want to type Not Available or text when the result of Sumif or Sumifs = 0 (Zero) Please advise

    1. Hello!
      Use IF function to get the result by condition. For example:

      =IF(SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")=0,"Not Available",SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete"))

  30. Long time follower....first time poster ;)

    Trying to set something up where I can get my MTD target based on day of month input.

    Example: If in Cell A1 I input '7' for the 7th day of Jan.

    In say cell B9, I want it to reflect the sum of daily targets in a table on a separate sheet.

    For simplicity sake: the table is C7:AG7, I would want B9 to be the SUM of C7 to I7.

    I can't figure this one out

  31. Hi. Please I’m trying to use this formula buts it’s only recognizing the security. It’s not summing up the security and electricity.

    =SUMIFS('259222496_1_59_0'!D12:D,'259222496_1_59_0'!I12:I,{"O.N- SECURITY","O.N- ELECTRICITY"},'259222496_1_59_0'!A12:A,">="&DATE(YEAR(B5),MONTH(B5),1),'259222496_1_59_0'!A12:A,"<="&EOMONTH(B5,0))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Explain what data you are using and what result you want to get.
      Also, notice the paragraph above: Using Excel SUMIF with multiple OR criteria.
      {“O.N- SECURITY”,”O.N- ELECTRICITY”} - this does not work.

      1. I want to sum up the figures with “O.N Security” and “O.N Electricity”. I haven’t able to do them individually with the formula but I’m trying to sum them together

        1. Hi!
          Read the paragraph I recommended. There is an answer to your question. I cannot write you a formula, as it contains unique references to your data, which I do not have.

  32. Quote value Days_to_close Salesman Quote_month
    234 90 Ram 01-Jan-15
    342 100 Satish 05-Feb-15
    122 34 Suresh 15-Apr-15
    544 67 Suresh 15-Nov-15
    232 99 Ramu 14-Nov-15
    533 89 Babu 29-Dec-15
    21 63 Suresh 01-Dec-15

    SUM of Quote_Value if Salesman name is Suresh and Days_To_Close is equal to or less than 90 and Quote_Month is equal to one of the following (Oct-15, Nov-15, or Dec-15).(using formulae)

    1. Hi!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

  33. Hi,

    I have jam and other stuff everywhere.
    I want to know how much jam is in the larder.
    So, trying to use SUMIFS, I have the following spreadsheet.

    My Σrange is all the numbers; My first criterion range is all the stuff; My first criterion is stuff: My second criterion range is all the places; My second criterion is places.
    It doesn’t work. Whyyyyyyy?

    In reality, I’ve a Census database of 'How many speak each of 200 languages in each of UK’s 1000 regions'.
    Finding how many speak something somewhere manually is a pain in the archive.

    (To simplify entering the two criteria, I’ve used ‘data validation’, with an input-list for each criterion.)

    Here's the spreadsheet - or it would be if it could be copied and pasted properly!

    criterion 2 Larder
    criterion 1 jam #VALUE! ←←

    places → Larder Cupboard Fridge
    stuff ↓ biscuits 10 20 3 ← numbers
    butter 3 0 1
    cheese 4 0 2
    jam 15 5 0

    ……....................................................................................
    Formula (in F16): SUMIFS (F19:H22, E19:E22, E14,F18:H18, F13)
    [the spaces are for easier reading only]
    --- In 'English': Sums if ( Σrange, C1range, C1, C2range, C2)

    1. Hello!
      All ranges of values in the SUMIFS function must be the same size. You can read about this in the article above. An example of your formula:

      =SUMIFS(E19:G22, I19:K22, E14, M19:O22, F13)

  34. I want a conditional formula for insurance calculation based on below criteria

    Gender Name Age Married/Single Insurance Calculation
    Male XYZ 16 Single
    Male XYZ 23 Single
    Male XYZ 58 Married
    Female XYZ 60 Married
    Female XYZ 25 Single
    Male XYZ 29 Married
    Female XYZ 32 Married

    Criteria:

    Gender/Status Amount Age
    Male/single 265.00 From 5 to 19 years
    Male/single 270.00 From 20 to 40 Years
    Male/single 300.00 from 41 to ETC
    Female/Single 270.00 From 5 to 19 years
    Female/Single 300.00 From 20 to 40 Years
    Female/Single 350.00 from 41 to ETC
    Male/Married 300.00 From 5 to 19 years
    Male/Married 350.00 From 20 to 40 Years
    Male/Married 400.00 from 41 to ETC
    Female/Married 337.00 From 5 to 19 years
    Female/Married 388.00 From 20 to 40 Years
    Female/Married 442.00 from 41 to ETC
    Female/Maternity 450.00
    Child 200.00

  35. Thanks a lot,
    it's worked..

  36. Hi, I hope you could help.

    I have a table where I need a sum to be displaying in the same cell, however it will vary but based on two options:

    Sum for option 1 multiplied by the quantity
    OR
    Sum for option 2 multiplied by the quantity

    Thank you in advance!

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice.
      Please describe all the conditions of the formula so that I can help you.

  37. Hi there

    I want my cell to look for a set name (construction ref. A7) in another sheet and if that is true display the ammount of money spent so far but ONLY if its a positive number

    Current construction has a loss of £500

    I would like the loss to be shown in a second column by repeating the fomula but with 0)

    but this is doing the maths of 900 minus -500 giving a return of 400

    I dont know if i need to be combing VLOOKUP or if it should be a count etc? im very lost

    1. sorry the fomula got lost there

      =SUMIF(Arnold_Sector,A7,Arnold_outcome)*(Arnold_outcome>0)

      1. Hello!
        Based on your description, it is hard to completely understand your task.
        To display only a positive amount, use the formula:

        =IF(SUMIF(B2:B9,A7,C2:C9)>0,SUMIF(B2:B9,A7,C2:C9),"")

        To calculate the sum of only positive values by condition, use the formula:

        =SUM((B2:B9=A7)*(C2:C9)*(C2:C9>0))

        1. Well i want it to comeplete the first task of looking to see if A7 is in the column, then look a the second column for the ammount related to it and add them up if theyre postive numbers

          For example, i have got it to count the ammount of Constructions (2) and the total spent 1400 and i want it to look at the individual spends as a positive or negative and only add them up if theyre a positive (i can repeat the task for negative in a seperate cell)

          thankyou for your help on this

  38. Trying to figure out how to sum a row with date columns in it.
    I want it to ignore negative numbers and the date.
    Such as the following
    "Buyer" "Amount" "Jan Date" "Amount" "Feb. Date" "Amount" "Mar. Date"............"Total Due"
    Fred $1000 1/1/21 -$300 2/10/21 $500 3/20/21
    Sally $300 1/20/21 -$100 3/10/21

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM((A2:A5="Fred")*(B2:B5>0)*B2:B5,(A2:A5="Fred")*(D2:D5>0)*D2:D5)

      If this is not what you wanted, please describe the problem in more detail.

  39. Is there an easier way to type this sort of formula?

    =SUMIFS(Sheet1!D:D;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!E:E;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!F:F;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!G:G;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)+SUMIFS(Sheet1!H:H;Sheet1!Q:Q;"TRUE";Sheet1!O:O;E16)

    ...and goes on like this for a few more columns. Using Excel 2016.
    Thanks

    1. Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Explain what you want to do.

  40. Good evening sir, i have some questions as below:
    1. Why need toput sum infront of sumif/sumifs? Any the result place sum(sumif/sumifs) is correct?
    2. is there any easy way to get value number from the value text? Exp: i wanna get 100 in number from the text $100.00?
    thanks

  41. I have created a weekly revenue record of a hospital spread over a month. I created it such that each sheet contains 3 month record by-weekly. Now my sheet 2 is supposed to be a summary sheet categorizing the income into the various hospital unit . I have used the SUMIFS Formula, it worked but could not place the revenue in its appropriate category.e.g revenue from pharmacy should go to pharmacy in the summary sheet while Revenue from laboratory should go to laboratory.but it is not so with what I created.I believe something is not right with my formula. What could be the problem and please can you help me out?
    Thanks

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. I recommend paying attention to the SUMIFS function. You can find the examples and detailed instructions in the article above.

  42. hai thanks for this page, i can build 2 criteria and with 3 criteria.

    with 2 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021")

    with 3 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021";$B$18:$B$501;"city")

  43. in sumif how we can get average no.

  44. Thank you for these great explanations and clear examples. They helped me automate some manual things I have done over the past few years, and will help me reduce errors. Thanks!

  45. Thank you for the formula and explanation of SUMIF(range;{array};range) to create an array of results and SUM(SUMIF(range;{array};range)) to sum the array of results into one cell! It helped me very much.

  46. Hi, The formula that I am using is =SUMIF('FA Journal'!A1:A10000,L51,'FA Journal'!J6:J615). I need this formula for over 5000 rows when I drag the formula down, the range (A1:10000 becomes A2:A10001, I want criteria to keep change as per the row number but not the range. How can I resolve this issue? Thanks

    1. You can use A:A for range, which helps a bunch while using sumif and suimfs functions

  47. I have a question about sumifs with multiple criteria. There is an example that shows products (orange, apple, banana), suppliers (Pete, Mike, ) and qty. I apply the similar logic/formula to a spreadsheet with a slightly different layout and it returns an error (#value). Imagine a spreadsheet where the name of the suppliers (Pete, Mike, etc.) are in row 1 and repeated across several columns multiple times. Now imagine in column A are the names of products (orange, banana, etc.) and they also appear in several rows multiple times. Inside the spreadsheet, where supplier/product intersects are the numbers (Qty). When I apply the same formula/logic), it returns error. My table has 10 columns (B to K) and 10 rows (A2 to A11). Here's my formula: =SUMIFS(B2:K11,B1:K1,"Pete",A2:A11,"apple"). This gives an error message (#value). Why wouldn't it count/add the quantities in the cells based on the criteria in this type of spreadsheet? I think it has something do with the table layout but can't figure it out.

    1. Hello!
      The SUMIFS function works with conditions in only one column, not a range of data. Therefore, in your case, this function cannot be used.
      Try this formula:

      =SUMPRODUCT(B2:M9*(A2:A9=N1)*(B1:M1=P1))
      N1 - product (apple)
      P1 - supplier (Pete)

      You can change it for your data.
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alexander, thank you but it is not working. I believe B2:M9 is the array (range) for the table/area that contains the values so that's what I selected. N1, P1, I used the values as +"apple", "pete" or was I supposed to create a separate tables to define P1 and N1? In any case, the formula resulted in error. Maybe you can test it at your end and send me an updated formula.

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. B2:M9 is the range that numbers (Qty) are in. N1 says "apple", P1 says "Pete". Column A says orange, apple, banana, apple, banana, etc. Row 1 says Pete, Mike, Pete, etc.
          What doesn't work for you?

  48. how to use for sumifs function for two tables example i have table 1 and table 2 have same structure with different data

    TABLE 1 TABLE 2
    TEAM 1 Credit TEAM 2 Credit
    Cameron Bancroft 8.5 Josh Philippe(w) 8.5
    Liam Livingstone 9 James Vince 9
    Mitchell Marsh 10 Daniel Hughes 10
    Colin Munro 8 Moises Henriques(c) 8
    Josh Inglis(w) 9 Jordan Silk 9
    Ashton Turner(c) 9 Daniel Christian 9
    Aaron Hardie 9 Carlos Brathwaite 9
    Jhye Richardson 10 Sean Abbott 10
    Andrew Tye 8 Ben Dwarshuis 8
    Jason Behrendorff 8 Steve OKeefe 8
    Fawad Ahmed 10 Jackson Bird 10

    i need output

    OUTPUT TO BE LIKE THIS
    PLAYING TEAM Credit
    Daniel Hughes 10
    Moises Henriques(c) 8
    Jordan Silk 9
    Cameron Bancroft 8.5
    Liam Livingstone 9
    Mitchell Marsh 10
    Colin Munro 8
    Josh Inglis(w) 9
    Fawad Ahmed 10

  49. Hi Alexander,

    I need your help with summarizing my data.
    I have data about 162 countries from 1970 to 2013. In each year, I have data about the frequency of s specific ethnic group. There are different ethnic groups for each country, and I'm trying to summarize for each country the data of all the frequencies in a given decade. I can't just roll the command of "sum" to all countries because they are all different in the number of ethnic group cells.
    What can I do?

    It looks like this:
    country year ethnic group freq SUM
    US 1970 muslim number SUMMARY for 1970
    US 1970 jewish number
    US 1970 romancatholic number
    US 1970 non.reli number
    US 1970 orthodox number
    Canada 1970 hindu number
    Canada 1970 sikh number
    Canada 1970 protestant number
    Canada 1970 buddhist number
    Canada 1970 jewish number
    Canada 1970 Muslim number
    Canada 1970 non.reli number

    1. Hello!
      Here is the article that may be helpful to you: Vlookup multiple matches in Excel with one or more criteria.
      I hope this article will be helpful. Unfortunately, you did not explain exactly which data you want to summarize. Your task is not very clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  50. great blog post

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