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

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

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

    PLS HELP ME

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

  4. Fantastic answers - thanks!!

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

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

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

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

  9. Dear,

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

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

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

  12. =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?

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

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

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

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

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

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

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

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

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

  22. Excellent examples and explanations - congratulations on your expertise

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

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

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

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

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

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

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

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

  31. I'm looking for a formula that tell me how many time it replaced the same part in different cars. see chart
    Car # Replaced Parts
    Car 1 Battery
    Car 5 Gas Tank
    Car 3 Windsheild
    Car 4 Battery
    Car 5 Gas Tank
    Car 2 Battery
    Car 4 Lock
    Car 1 Filter
    Car 1 Battery
    Car 5 Windsheild
    Car 3 Gas Tank

    I replace the Gas Tank 3 time in different (2) cars. But I want to know how many times I replace the Gas Tank in different cars.
    Thanks in advance.

  32. CAN YOU HELP WITH A FORMULA TO DETERMINE THE NUMBER OF TIMES "NO SHOW OR CX" APPEARS IN RELATION TO A CELL? FOR EXAMPLE TEXT IN CELLS L40:L150 SPLIT
    CELLS N40:N150 WOULD HAVE EITHER CX OR NOW SHOW LISTED CAN I GET A FORMULA INDICATING A TOTAL OF NO SHOW AND CX WITHOUT ADDING ANOTHER COLUMN?
    THIS IS WHAT i HAVE TRIED BUT CANNOT GET IT TO WORK:
    =COUNTIFS(L40:L150,"SPLIT",N40:N150,"NO SHOW",N40:N150,"CX")

  33. if there is a error cell in the sum range then how we will use this formula

  34. M5 is Starting Date and I5 is Ending date

  35. =SUMIFS(SAmount,SItmName,H8,SaleDate,">=M5",SaleDate,"<=I5")

    I am writing sumifs formula in that way but it did not calculate, Please one who help me regarding to this

    Thanks

  36. Good post, thanks. I have a followup question. I am currently using this SUMIF($D$3:$D$22,"=1",$B$3:$B$22). Now instead of =1, if I want to provide a cell location it doesnt work. How do I do this? For example, SUMIF($D$3:$D$22,"=K43",$B$3:$B$22)

    1. Any idea on this? When I try this it calculates incorrectly. Do I need to use quotes differently?

      1. Hello Arun R,

        I had the same problem and solved it as per the below change.

        SUMIF($D$3:$D$22,"="&K43,$B$3:$B$22)

        Hope it works for you too!

  37. i think i'm over thinking it but I can not get the either or value to function
    column g has Yes or no in column h i want it to look at column g and if it is yes populate $250 if it is no populate $150. =IF(G41="NO",150,)*OR(G41="yes",250)
    the yes is giving a $0.0 result.
    What am I doing wrong?

  38. Help!

    Column F = Account Coding Letters ( I will input data into this resembling an account code e.g 001)
    Column G = Production coding Letters (I will input data into this resembling initials for someones name e.g PH)
    Column H = Net Price of what is spent (e.g £25

    Column K = NEEDS TO BE ABLE TO INPUT ANY CODING LETTER FROM EITHER COLUMN F OR G
    Column L = Needs to take the sum of the net price that is related to the coding letter that is inputed from column K.

    For example say if Polly Holloway bought fuel and I have an account code for fuel that is 001 and an initial code for Lauren which is LH)

    I need to be able to input into column L either of the codes 001 or PH (They will never be input together in the same cell) All I need to know how many times Polly bought fuel on our company and how many times fuel was bought altogether.

    So in a sense column K needs to be have a range of columns F & G

    At the moment the formula I have in column L is only applicable to Column G.

    Please see below:
    =SUMIF(G$9:G$44,K23,H$9:H$44)

    What is the formula that I can put into Column L so it applies to both Column G & Column F?

    Please help!

  39. I am using a formula in Excel spreadsheets that works great but it doesn't work in Excel 2003. Can you tell me what formula I need to use in 2003 - 2007 to get the same result as excel 2010

    =SUMIFS(D2:D80,$B$2:$B$80,"Overtime Paid",D2:D80,">0:00")

  40. I am using a formula in Excel spreadsheets that works great but it doesn't work in Excel 2003. Can you tell me what formula I need to use in 2003 - 2007 to get the same result as excel 2010

    =SUMIFS(D2:D80,$B$2:$B$80,"Overtime Paid",D2:D80,">0:00")

  41. What if I don't want to sum? What if I am looking at listing the commissions that a salesperson received and for what company they sold to. Let's say that the salesperson has a code - 1, 2, 3 and so on, in column A. The customer name is in column B. I want the formula to read column A to the bottom and return the name of every customer they sold to in another tab in column H. The answer would look like this;

    Salesperson Customer
    7 Apple, McDonalds, Chevron

    Can you tell me what the formula would be for that?

    Thanks!
    Valerie

  42. Hi,

    I am having an issue but not 100% if this solves it or not. It involves trying to have different sums performed based on multiple selection criteria.

    The idea is to have 2 criteria which can be selected by dropdown lists. The user simply picks the 2 criteria and then places an "X" in the rows he requires. The pricing calculator then works out a cost.

    For example if a certain service (Service A, Service B, Service C) is selected from a dropdown list, and a currency is selected from the second drop down list then perform a certain calculation based on thatcriteria.

    In this example, if Service A is selected with currency = "GBP" then it would perform a sumif from the GBP price column for that service. If Service A is selected with currency = USD, it will perform a sumif from the USD price column for that service.

    I have it working for when only one criteria is applied but cannot seem to get it for both. In the below formula, column B is where the user puts "x" in and then column K has the GBP cost. The formula needs to use a different column for the USD cost (column L).

    =IF(G6="Service A",SUMIF(B21:B117,"x",K21:K117), "")

    the above formula should read something like IF G6 =Service A AND G7 = "GBP" then do the sum using column K. IF G6 = Service A AND G7 = "USD" then do the sum but using column L.

    If anyone knows how to do this, any help would be appreciated.

    Thanks
    Stephen

  43. I am trying to calculate more than one quantity but cant find the formula to use. Example in column d I have quantity in column e I have Retail price in column e I would like for the total of all the quanitiy at a total. Please help

  44. Very Helpful Svetlana!

  45. hi Svetlana, this was a great post!

    i'm hoping you can help me out on a quick question. you used an array argument in your formula in Example 2 of 'Excel SUMIFS with multiple OR criteria'

    my question is, how can i reference an array argument from a cell, instead of typing it in a formula:

    i want to reference the array argument {"Mike", "John", "Pete"} from just one cell instead of in a formula as you have in the following:

    SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))

    thanks for any help on this!

  46. This is what I used and it worked, but I'm kind of skeptical.

    =SUMPRODUCT(C3:C19,$D3:$D19)

  47. I have column B and C of % weight that correspond with $figure in column D. Column B belongs to partner Arthur and Column B belongs to partner Roxy. Column D has total annual expenses for each category e.g. Travel, Computer Purchase...

    How would I construct a formula to look into column B identify the % weight, multiply it to $ figure in Column D and sums up the result. Keep in mind that each % weight in the cell can be different so the formula has to identify the weight and than get the corresponding dollar figure for each cell and sum those up together.

    1. Hello Vince,
      I am sorry, it is quite difficult to understand the task without seeing your data. If you can send a small sample workbook to support@ablebits.com, we’ll be able to assist you better.

  48. Any Body can help me to calcuate this

    in A column have product name
    in B have its price
    in C column have mention its quantity
    that database is to 13 rows
    i want grand total of B multipal with C of each row.
    Thank You

    2. Hello Abdul,
      =SUMPRODUCT(B2:B13,C2:C13)

  49. I am new to SUMIFS functions but seem to be following the formula but get incorrect result.
    Using 2 worksheets in the spreadsheet.

    First Worksheet - multiple entries for each contract
    Column A contract number
    Column B Date
    Column C Costs (Amount)

    second worksheet - one entry for each contract
    Column A contract number
    Column B Date

    So in the 2nd worksheet I want to sum all costs (on 1st worksheet) for each contract that occured after a certain date.

    So I have tried the following forumla

    SUMIFS(worksheet1$C$1:$C$2000),worksheet1$A$1:$A$20000,worksheet2"$A2",worksheet1$B$1:$B$20000,">=worksheet 1$B2")

    When I leave the date criteria off I get the correct figure for the complete total but when I try to add the date criteria I get a value of 0.

    1. Hello Rachel,

      Please use this formula:
      =SUMIFS(worksheet1!$C$1:$C$2000,worksheet1!$A$1:$A$20000,worksheet2!$A2,worksheet1!$B$1:$B$20000,">="&worksheet1!$B2)

      Most likely the issue is due to this part: ">="&worksheet1!$B2

      1. I was having the same problem as Rachel. Alexander's solution worked for me. Stills seems like unnecessarily complicated syntax, but it works.

  50. Hi Svetlana

    Column A: Project ref
    Column B: Total hours on Project in one day
    Column C: Total hours on one project overall
    Rows = Days of the week

    Column A consists of multiple reference numbers which repeat if a project is worked on for more than one day, but not necessarily on consecutive days. I want to be able to cumulatively add the total hours worked on a project over multiple days using a sumif calculation. For example i would like the total number of hours worked on project ref 222 over several non consecutive days in one month. The project ref range is large and therefore i do not want to specify "222" in the criteria but all instances where a reference number is repeated in the ref column.

    Can you please help.

    Thanks

    Dan

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