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 2. Total comments: 234

  1. How would I write a modified formula for the example using this base formula.

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

    What if I wanted to another set of criteria for "products" to include "cherries" and "apples".

    Essentially, I want the sum of the cross section of 2 sets of criteria in 2 separate criteria ranges. Ideally, I would like to do cell references for the criteria against its range and not hard code the criteria.

      1. Here is the brake down: want a SUMIFS formula that considers the following. I want the sum total of all the cross-sections of 3) & 5)

        1) SUM RANGE: ABC'!CP:CP

        2) Criteria_range1: ABC'!$C:$C

        3) criterion1 (range): A7:A8

        4) Criteria_range2: ABC'!$F:$F

        I hope that makes sense.

        5) criterion1 (range): XYZ'!$C:1$C3

  2. How to use SUMIF formula to collect value for same criterion from multiple sheets in same workbook.

    E.g., Criterion is "TDS 22-23" in column B, Range is column A and value is in column C.
    Value to be summed in from sheet 1 to sheet 4 of workbook in sheet 5.

    Kindly help. Thanks

  3. Hey! I have two columns that I am working with: GL Account | Amount

    The first part of the formula is fine which is =sum(sumif(B:B,{"GL Account","GL Account","GLAccount"},D:D))

    The problem I am running into is that for one of the accounts I only want the number if it is equal to or > than 0 so to the above formula i tried +sumif(B:B,"GL Account",D:D,">=0") but had no luck.

    Please help!

    1. Hi!
      If you have many criteria, use the SUMIFS function:

      =SUMIFS(D:D,B:B,"GL Account",D:D,">=0")

      For more information and examples, see article above.

  4. I need to count minutes with 3 criteria:
    I need to Sum the number of minutes (D cell) if A cell includes text with the required hotel name
    and the time between 08:00 - 19:59 (and 20:00 - 07:59)

    My columns:

    A(Hotel name) B(Start Time) C(End Time) D(Duration)
    AHotel Blabla 17:03:43 17:04:43 1
    BHotel Blabla 17:02:43 17:04:43 2
    AHotel Blabla 08:03:43 08:04:43 1
    CHotel Blabla 08:02:43 08:04:43 2

    I am trying:

    =SUMIFS(D:D,A:A,"*AHotel*",B:B,">=08:00:00",B:B,"<=19:59:00")

    It doesn't work, please help me...

  5. Hello,

    I am trying to total 2 sets of values, but only if their checkboxes are checked. I have a set of checkboxes in cells B9, B11, B13 & B15 and values for these in column E, and another set of checkboxes in cells B26, B28 & B30 with their values also in column E.

    For the first set I have written the formula =SUMIFS(E8:E15, B8:B15, TRUE) in cell F16, which works. However, I only want this to calculate if none of the checkboxes in cells B26, B28 & B30 are checked. What formula should i use instead?

    Also, the values for the second set only need to be included in the total too, if all B9, B11, B13 & B15 checkboxes in the first set are checked, so for this I have written the formula =IF(AND(B9=TRUE,B11=TRUE,B13=TRUE,B15=TRUE),SUM(E8:E15),"") in cell F31, which also works.

    However I want to also include the values of the second set in cell F31 too, but only if their checkboxes (B26, B28 & B30) are also checked. What formula should I use for this?

      1. Hi Alexander,

        Brilliant as ever, that works great, thanks!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  28. Hello,
    I have multiple columns I need to be able to sort Month by Month and then I have 9 columns that I need to find a % of based off the date or the month.
    Ex: DOB, Male, Female, in a relationship, single, bald, has hair, Left handed, right handed. What % of males are single, bald and right handed.
    Any help would be greatly appreciated!

  29. Hello there,

    =SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)

    this sums regardless of any product, what if i want it to sum based on the product without declaring it in the formula

    Ex: get me the sum of Mike and Pete if the prodcuts were a match
    it gets me the oranges sum alone and the apples alone without me specifying it in the formula

    could you help please?

    1. Hello!
      I recommend using the SUMIFS function, which calculates the amount according to several conditions. Please read the above article carefully.

  30. I am trying to create a SUMIF formula for a variety of fields.
    This is my current formula
    =SUMIFS('General Acct - 2020 Debits & Credits'!$G$581:$G$648,'General Acct - 2020 Debits & Credits'!$B$581:$B$648,{"Transfer to Secured Acct (Special Projects)","Transfer to Reserve Acct","Transfer to Hydro Acct"},'General Acct - 2020 Debits & Credits'!$C$581:$C$648,"Other")

    The just of it is that if any of the "Transfer Accts" are chosen in column B and "Other" is chosen in Column C then then sum all amounts associated in column G (debit column)

    right now if just returns 1 individual amount

    1. Hello Erika!
      If I understand your task correctly, you can use the SUM function:

      =SUM(SUMIFS(‘General Acct – 2020 Debits & Credits’!$G$581:$G$648,’General Acct – 2020 Debits & Credits’!$B$581:$B$648,{“Transfer to Secured Acct (Special Projects)”,”Transfer to Reserve Acct”,”Transfer to Hydro Acct”},’General Acct – 2020 Debits & Credits’!$C$581:$C$648,”Other”))

      If there is anything else I can help you with, please let me know.

  31. Hi
    Please help me with this table
    I have this table. A,B,C are the excel columns and 1,2 3 .... are the rows.

    A B C
    1 Daily Price update
    2 Apple 2.6
    3 Banana 10
    4
    5 Type of fruit Last days price Today’s price
    6 10

    My requirement is that when I make an entry in A6 (type of fruit), it should check value from A2 or A3
    Then it should add value of B6 as per values given in B2 or B3 as per entered value.

  32. Hi,
    Can the one condition in Sumifs be a row condition and another a column condition? The numbers of rows in the row condition and number of columns in the column condition are the same as the sum range, but I still get a #VALUE result

    1. Try looking for the formula =SUMIF(INDEX(MATCH)

  33. Hi may someone help in this,
    I want to sum only the negative numbers from U column. What should I add to this formula?
    =SUMIFS('Detailed accounting'!$U:$U,'Detailed accounting'!$N:$N,$A9,'Detailed accounting'!$C:$C,B$2,'Detailed accounting'!$K:$K,"USD")

    Thanks

    1. =SUMIFS('Detailed accounting'!$U:$U,'Detailed accounting'!$U:$U,"<0",'Detailed accounting'!$N:$N,$A9,'Detailed accounting'!$C:$C,B$2,'Detailed accounting'!$K:$K,"USD")

  34. I am trying to create a formula that does the following and I cant get the formula to work for all. only one or two of them. but not all of them.
    Need a sum for Column J
    if Column E = the words Growth or Acquisition
    if Column K = the month of July, 2019
    So far I have this;
    =SUMIFS($J$2:$J$142,$E$2:$E$142,"Growth")+SUMIFS($J$2:$J$142,$E$2:$E$142,"Aquisition")+SUMIFS($J$2:$J$142,$K$2:$K$142,">="&$L$145,$K$2:$K$142,"="&$L$146,$K$2:$K$142,"<="&$M$146))

    But its counting the amounts multiple times

    1. I have an updated formula. It is working and not erring out or counting the amounts multiple times however its not catching all amounts.
      =SUMIFS($J$2:$J$137,$E$2:$E$137,{"Growth","Acquisition"},$K$2:$K$137,">="&L140,$K$2:$K$137,"<="&M140)

  35. For the second example, if i wanted to return multiple Criteria 1 for example Apples and Oranges Pete has supplied how would I do that?

    1. Hi Chris,
      You can do that with one of the below formulas:
      =SUM(SUMIFS(C2:C9, A2:A9, {"apples","oranges"}, B2:B9,"pete"))

      =SUMIFS(C2:C9, A2:A9,"apples", B2:B9, "pete") + SUMIFS(C2:C9, A2:A9,"oranges", B2:B9, "pete")

      1. Thanks for that it was perfectly. although in some instances i have over 20 criteria and calculating is taking quite a long time. is there a faster way to calculate?

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

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

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

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

  41. How do I get the total of all Fruit NOT sold by Mike?

    1. Hi Sheil,

      You can use a SUMIF formula similar to this:
      =SUMIF(B2:B10, "<>"&"mike", C2:C10)

      Where B is the column containing the names and C is the column to sum.

  42. ... might be nice to point out that the ranges cannot be in adjacent worksheets within a workbook, but must be on the same sheet as the cell in which the formula is entered. I've been trying for an hour to find out what's wrong with my SUMIFS using the sum range and one of the two criteria ranges on another worksheet in the workbook. Always got a 0. Then I moved it all to the same worksheet and it worked fine. Waste of an hour, though...

    1. Hi Dave,

      In fact, the sum range and criteria ranges can be on different worksheets provided that all the ranges are of the same size. I've just tested a simple formula like =SUMIFS(Sheet1!A1:A4, Sheet2!A1:A4, "a", Sheet3!A1:A4, "b") and it worked just fine.

  43. Hi,

    Sorry if I missed this in your post. I am using the below formula:

    =SUM(IF(FREQUENCY(A2:A367,A2:A367)>0,1))+SUM(IF(FREQUENCY('Sheet2'!A2:A367,'Sheet2'!A2:A367)>0,1))

    This gives me a total unique count from two separate sheets. However I now want to count the unique values with criteria attached. I tried the following:

    =SUMIFS(IF(FREQUENCY(A2:A367,A2:A367)>0,1),B2:B4367,">="&Settings!B6, B2:B367,"0,1),Sheet2!B2:B367,">="&Settings!B6,Sheet2!B2:B367,"<="&Settings!B5)

    However it only comes up with a #Value! error. I feel as though I am missing something obvious with the structure of the nested IF within the SUMIF but I can't see it.

    Thanks for any help :)

    1. Just as an update, I attempted nesting the IF functions against the criteria and against the range manually but I was still unable to retrieve a functional result.

      1. Thank you for your reply. I worked it out as follows:

        =SUM(IF(FREQUENCY(IF(CUKD>=Settings!B8,IF(CUKT"",MATCH("~"&CUKT,CUKT&"",0))),ROW(CUKT)-ROW('Current Year UK'!A2)+1),1))+SUM(IF(FREQUENCY(IF(CUSD>=Settings!B8,IF(CUST"",MATCH("~"&CUST,CUST&"",0))),ROW(CUST)-ROW('Current Year US'!A2)+1),1))

        The structure was flawed in that the criteria was being applied but not as part of the SUMIF function, at least not correctly. It was far easier instead to simply specify nested IF functions as above and push as an Array. The named ranges are simply just to reduce loading time for adjusted ranges, it will eventually become a requirement to use SQL to perform the same function, but by that point I think I will migrate from Excel altogether.

  44. Dear Svetlana,

    I am struggling with the following formula. I would like to sum a1:a200 if b1:b200 contains patrial text match "CMCM*" AND "CMME*" AND "CMCO*", but also if c1:c200 matches with {'sheet2'!a1:a50} (so any match in that range)

    I came up with =SUMIFS($A$1:$A$200;$B$1:$B$200;{"CMCM*";CMME*";"CMCO*"};$C$1:$C$200;{'sheet2'!A1:A50}).

    The issue in this is that it does sum some of the matches, but I want to sum all possibilities.

    Could you help me find the right formula? I hope it is possible.

    Best regards, Bram

    1. I found out how it works. The following formula does the trick:

      =SUMPRODUCT(--(ISNUMBER(MATCH($C$1:$C$200;'sheet2'!$A$1:$A$50;0)));--(ISNUMBER(MATCH($B$1:$B$200;Formula!$A$1:$A$3;0)));$A$1:$A$200)

      where Formula!$A$1:$A$3 contains the partial match criteria.

  45. Summing Across a Row?

    I thought this would be relatively simple but I cannot figure a way to do it (granted I am not a superuser - yet). Here is the problem:

    Given a ROW of data as follows:

    A,3,B,6,A,5,A,2,B,7

    I want to sum all the values which follow an A and then sum all the values which follow a B. So output would be in two cells one for A values which would equal 10 and the other for B values which would equal 13.

    I tried using SUMIF array formula but maybe that is not possible. I would hate to have to use a combination of simple IFs as the rows are long and there are a lot of them so thought array formula might help. Any suggestions?

    1. Hello, Eddie,

      If the number of values in rows is fixed, you can use this formula:
      =SUM(F12*IF(E12="A", 1, 0), H12*IF(G12="A", 1, 0),J12*IF(I12="A", 1, 0),L12*IF(K12="A", 1, 0),N12*IF(M12="A", 1, 0))
      =SUM(F12*IF(E12="B", 1, 0), H12*IF(G12="B", 1, 0),J12*IF(I12="B", 1, 0),L12*IF(K12="B", 1, 0),N12*IF(M12="B", 1, 0))

      If the rows length varies you need this array formula:
      {=SUM(IF(OFFSET(B1:K1, 0, -1) = "A", 1, 0) * IF(ISNUMBER(B1:K1), B1:K1, 0))}
      {=SUM(IF(OFFSET(B1:K1, 0, -1) = "B", 1, 0) * IF(ISNUMBER(B1:K1), B1:K1, 0))}

  46. Can you provide me with a basic difference between SUMIF and SUMIFS?

    Thank you.

    1. Hello Julie,

      SUMIF adds up cells based on 1 criterion, SUMIFS allows for multiple criteria.

  47. Hi,
    I want use the formula for to get sum of column F:F as against
    =SUMIFS($F:$F,$A:$A,$B$4:$B$101,$B:$B,$A4)

    Here Critera1 is the range($B$4:$B$10)
    not a single value , how can I modify the formula to get the sum

    Thanks & Regards,
    Giri

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

      =SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30))
      could you please help me to use the range D1:D3 instead of typeing the values like{50861394,50861765,50861767}

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)