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

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

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

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

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

    even though the above it's wrong

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

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

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

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

  4. Respected Sir,

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

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

  5. i need to devolop sum if for tow condition

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

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

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

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

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

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

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

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

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

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

    can you help?

  11. Appreciate your help with the below formula..
    Col A Col B
    >=10M 10
    =10M 30
    using the below formula
    =SUMIFS(B2:B4,A2:A4,">=10M")
    I am getting result 0 but expecting 40...Using Sumifs as there are other criteria as well along with the above formule..
    Thanks in advance..

    1. I see some data is missing..
      so to add on to above ">=10M" is a static value not an operator..

  12. Hi,

    Is it possible to get the value of a specific cell as your criteria1 or criteria2 ? ex. =SUMIFS(T8:T109,R8:R109,"value inside cell AE6",S8:S109,"value inside cell AE7")

    thank you very much for your response. :)

  13. thanks

  14. Dear
    Greetings..!

    I'm trying to calculate SUMIF function but i do not know how to calculate and getting my answer but i would expecting you guys will solve my problem in excel.

    i have an excel sheet that contain in Column (A) Date as (1-1-2016 1:00 PM) in Column (B) Names Like (Adam, John, Wiki) in Column (C) an Amount such as 100, 200, 500 in Column (D) Should be My Answer that's are follows

    if in Column (A) Date "1-1-2016 to 31-1-2016" and in Column (B) "Adam" then Calculate the amount in Column (C) "100" and show the Addition in Column (D)

    i tried this but i'm fail

    =SUMIFS(C1:C3,B1:B3,"Adam")+SUMIFS(C1:C3,B1:B3,">=1-01-2016",A1:A3,"<=31-01-2016")

    Kindly Help me
    Thanks in Advance

    1. =SUM(SUMIFS(C2:C18,B2:B18,"Del",A2:A18,">=1-Jan-17",A2:A18,"<=15-Jan-17"))

  15. Question regarding Cells Contain non empty "alone".....

    Sum Values in Cells C2:C10, if the Corresponding cells in Columns A2,A5,A6,A10's are filled in with totals, remaining A3,A4,A7,A9's are filled in with values. Now I want Sum only Filled in with totals and not values and next condition is totals <10000 should come. I tried below formula but all values are coming(A2,A3,A4,A5,A6,A7,A8,A9,A10).

    =SUMIFS(C2:C10,A2:A10,"",C2:C10,"<10000")

    Please give me some solution for the above, Thanking You

  16. Hi, thanks for your great article, this helped a lot!

    Can you please help me to split an array out of the formula into a different cell to make it easier to update?

    =SUMPRODUCT(SUMIF('data'!$B:$B,{"Apples","Oranges","Pears"},'data'!$J:$J))

    In the above formula, I wish to change it to something like =SUMPRODUCT(SUMIF('data'!$B:$B,C3,'data'!$J:$J)), where C3 contains {"Apples","Oranges","Pears"}.

    Is this possible?

    Thanks!

  17. In columns A, B & C I have a list of names. All the names are the same for each column just not each column will have a complete list of all the names nor will it be in order. (See below). My question is this: I need a formula in column D that will tell me
    1) if a name in column C matches a name in columns A and B to say "good"
    2) if the name in column C matches a name in column A but not B to say "scanned"
    3) if the name in column C matches a name in column B but not in column A to say "timesheet".

    Example:
    A B C D
    Adriana Alvin Alvin GOOD
    Alvin Tony N/A N/A
    Anthony Adriana Anthony SCANNED
    Sam Sam Tony TIMESHEET

  18. HI,

    USING IF CONDITION HOW WE CAN ADD VALUES LIKE

    A1 APPLE 03PCS
    A2 ORANGE 8PCS
    A3 BANANA 2PCS
    A4 APPLE 13PCS
    A5 APPLE 07PCS
    A6 ORANGE 10PCS
    A7 BANANA 4PCS

    IF I NEED TO CALCULATE ONLY THE QUANTITY OF AN APPLE USING IF CONDITION WHAT WOULD BE THE FORMULA

  19. Hello, I'm a beginner and a teacher who really needs help with creating my formula. I want the formula to report the number in column 1 if the numbers in the column is between 1 - 4 but add column 1 to 2 if column 1 is 5 or more. If column 1 and 2 is greater than 10, I want it to add column 3 otherwise just say the sum of 1 and 2. This goes on for quite some time and so the function often breaks or doesn't work properly. Any tips on how to do this? I tried to write a formula with the if function but it didn't work well. I'd be grateful for any and every tip. Thanks, T.

    1. Hi!

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(A2<5, A2, IF(A2+B2>10, A2+B2+C2, A2+B2))

  20. please see attached

  21. Hi Gabriel

    =IFERROR((Z19/AD19),0)

    or

    =IF(AA19=0,0,Z19/AA19)

    I would do the first one personally. Hope it helps!

    Michel, I would just add another column with =IF(G2<100,SUM(G2*250)*H2,H2*G2)

  22. I was hoping someone might give me some insight into how I can combine these two functions:

    =SUM(Z19/AD19)
    =IF(AD19,Z19/AD19,0)

    The SUM is delivering a #DIV/0!

    This leads to the IF statement which delivers a "0" so it can be read by some conditional formatting I have listed.

    I need the cell to be able to read the SUM and calculate when it can (and it does) but if the division is by "0" and I get the #DIV/0! I need that IF calculation to produce the "0"

    Can anyone help please?

    Gabriel

  23. Hi

    I want to include a SUMIF in a SUMPROUCT formula where a value should change if it is lower than a specific value.

    =SUMPRODUCT((G2:G36,H2:H36)/SUMIF(G2:G36,"<100", G2:G36*250))

    The above function does not work, however, if value(number) in the "G" row is lower than 100, it should be multiplied by 250.

    I cannot get it to work...hope you can help.

  24. Thanks

  25. Hi i have a List of number that i wanted to know which of the SUM(combination) that will match with my predefine value.
    Example:
    In column A1 i have this list of number
    100,110,130,150,160

    and i wanted to know which of the summation will get 260

  26. i want to sum non-blanks in a row of data after bypassing first three non-blanks. Means I have to sum from the start of 4th non-blank entries without considering first three non-blanks. please support.

  27. Hello,
    please I need to fix formula for:
    I have to make 3 flights within 90 days.
    in C/D/E I have dates of each flight. I have to know if 3 flights are done within 90 days. and according to last date-3rd date ( if 3 flights are done in 90 days)I have to know expiry date, means + 90
    thanks

  28. I have an excel file where I capture the estimated working hours of the employee from the website in Sheet1(employee working hours), I need to capture the estimated and actual hours, whichever has the higher hours will be shown in the cell in sheet2(Utilization sheet).
    =SUMIFS('From employee working hours'!P:P,'From employee working hours'!Y:Y,'Utilization sheet'!D5,'From employee working hours'!K:K,'Utilization sheet!$C$1, 'From Dashboard'!U:U,"")
    - it shows the estimated working hours, the actual hours is from From employee working hours'!Q:Q

  29. hi How can i convert 10010101 into Aplha-numeric A-01-01-01 where digit at first two places 10=A

  30. please show me how to calculate 8 in a cell + RDO in another cell to equel 16

  31. Hello, need help. I want to calculate the amount payable to driver "A" for the pickups and/or deliveries done between 19:00hrs & 01:00 hour on 08/24/16. In this example he should get $10.

    No Pdate Ptime Ddate Dtime Amt$
    A 8/24/2016 2:10 8/24/2016 5:40 2.00
    A 8/24/2016 5:45 8/24/2016 7:40 3.00
    A 8/24/2016 17:58 8/24/2016 19:39 5.00
    A 8/24/2016 20:30 8/24/2016 22:01 3.00
    A 8/24/2016 23:50 8/25/2016 1:05 2.00

  32. No PickupDt PickupTime Delivery Date Delivery Time Drv($)
    A 8/24/2016 2:108/24/2016 5:40 2.00
    A 8/24/2016 5:45 8/24/2016 7:40 3.00
    A 8/24/2016 17:58 8/24/2016 19:39 5.00
    A 8/24/2016 20:30 8/24/2016 22:01 3.00
    A 8/24/2016 23:50 8/25/2016 1:05 2.00

  33. I need to calculate the amount payable to person A for date 8/22/16 the pickup time and/or delivery time between 19:00hrs & 01:00hrs

    No Pickup Date Pickup Time Delivery Date Delivery Time Drv($)
    A8/24/2016 2:10 8/24/2016 5:40 2.00
    A 8/24/2016 5:45 8/24/2016 7:40 3.00
    A 8/24/2016 17:58 8/24/2016 19:39 5.00
    A 8/24/2016 20:30 8/24/2016 22:01 3.00
    A 8/24/2016 23:50 8/25/2016 1:05 2.00

  34. give some sumif and countifs sumifs,countifs,avg
    avgifs problematic examples

  35. Hi,

    Can you plz tell me how to solve below mentioned issue.

    =SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/1/2016")

    When I put this formula it is working, but what I want is,to change the month of the date when I drag it to other cells in the same raw. It means, the month has to be changed automatically, January 2016, February 2016, March 2016.

    =SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/1/2016")
    =SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/2/2016")
    =SUMIFS(B23:R23,B26:R26,"PAID",B20:R20,"=1/3/2016") like this.

    How can I get changed only month automatically in this formula. Because all the time I have to change the month of the date manually. Please assist

  36. Dear Admin User,

    Please help me, my problem is mentioned the bellow.

    1
    1202 Need 12000 =sumifs(a6:a10,">5000",b6:b10,"=1",c6:c10,a4)

    5000 1 1203
    6000 2 1202
    6000 2 1202
    5000 1 1203
    5000 2 1203
    5000 2 1202
    5000 1 1202

    Thanks,

  37. I want to take a single criteria from one column, take a multiple criteria from another column and then sum the final column if the criteria from the first two are there. I know in your world this is a absolutely simple but I'm struggling. I'm looking for PFV in column C,if we have a PFV in column C then we also need 1,2,5,6,45,46,49 or 50 in column E, I then need the sum of the relevant figures in column F. I have to do this for over 100 separate tabs! Thanks for any help you can give me!

  38. In my column A I've all the first name and column B is the last name. When I try to do a sumifs to add any names in Column A it won't give me any result. However, when I do the same sumifs for column B it give me the correct result. How come they can't search the names in Column A. Please advise. Thanks

    1. Hi Frank,

      Sorry, it's not possible to determine the source of the problem without seeing the original data. Try typing some names manually in a new sheet, and then try your formula. If it works, then the problem is with the names in the original worksheet, and if it doesn't, then double check the formula.

  39. #DIV/0!
    =AVERAGEIFS(Yarn!O$2:O$10000,Yarn!B$2:B$10000,Sheet7!C$1,Yarn!I$2:I$10000,Sheet7!A$3:A$135,Yarn!J$2:J$10000,Sheet7!B$3:B$135)
    help me please

  40. Hello Dear All,

    I Use Excel 2007, i want to make
    E8:E107= Product (Desktop), G8:G107=Technician (Milon), T8:T107=Total Product Value.

    i Need to Show Only milon how mutch Keep from Desktop

    1. Suppliers amount
      Peter 128089
      Peter 128090
      Peter 128091
      Peter 128092
      Peter 128093
      Peter 128094
      Peter 128095
      Peter 128096
      Peter 128097
      Peter 128098
      Peter 128099
      Peter 128100
      Peter 128101
      John 21654114
      John 21654115
      John 21654116
      John 21654117
      John 21654118
      John 21654119
      John 21654120
      John 21654121
      John 21654122
      John 21654123
      John 21654124
      John 21654125
      John 21654126
      John 21654127
      John 21654128
      John 21654129
      John 21654130
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Tom 48418
      Can you help me for preparing summary sheet?
      there're more than 15 suppliers so I wanted to prepare a summary that all of them come just in one sheet.

      1. How to prepare Monthly Payroll in Excel Sheet ?

  41. help me please now and today if possible.
    "i have to award DIVISION I, II, III, IV and 0 to my students who have scored different grades in their subjects either A, B, C, D and F where each carries different POINTS such as A=1, B=2,C=3, D=4 and F=5.And note that the minimum subject to be taken by a student is 7 and the maximum is 10 while others can take 8 or 9 is allowed. As well as award division is equivalent to the following points; for Div I =(1-7)points, Div II = (8-12)points, Div III = (13-24)points, Div IV = (25-33)points, Div 0 = (34-35)points. Note that the awarded Division to any students regards/considers to the first seven subject scored highest others less than 7 subjects awarded as INC - incomplete. Thus core subjects are 7 among the taken ones. plz help which formula or function am to apply to award for my class students now.
    thanks and be blessed abundantly by God. Amen.

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

  43. Hi, I am trying to calculate a formula for the following data.

    t1 t2 t3 TOTAL
    -----------------------------
    10 10* 10* 20

    Critria:
    -In this I'm interested in sum of only * marks

    like in this total of 3 tests is 20 because there two stars of 10 marks.

    Can anyone please guide me?

  44. It is Amazing.

    I Love it

  45. Hi, I am trying to calculate a formula for the following data.

    Name Gender Grade
    ---- ------ -----
    ABC Male A+
    DEF Male A
    ACD Female A+
    ASD Male A
    SDF Female B
    AED Male A+
    LKJ Male A
    LOK Female B

    I would like to calculate Following things.

    1) Count of Male Students whose Grade is A+?
    2) Count of FeMale Students whose Grade is A?

    I couldn't able to write the excel formula to find the above things.

    Can anyone guide me?

  46. Hello,

    I am trying to come up with a formula that populates a sum of numbers from a different sheet (data source) - The tricky part is, I need the formula to cover a few different scenarios.

    Here are the things i need to cover:

    1. I need all of the items in a certain status (located in one column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column)

    2. I need all of the items in 3 different status' (located in the same column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column).

    The table I am trying to fill is on a different worksheet within the same workbook. I am having difficulty determining what formula set(s) to use to accomplish this. I will need to repeat the process weekly to have a running total - The data source will also be updated weekly, so these numbers need to be active, not static.

    Example:

    I need the total of all measures (items) that are in status "1-Template", status "2-Configuration", status "2.1-Reconfig" that were completed between 6/3 - 6/9 - I need this number to be populated into a table I created on sheet 2.

    Next, I need all of the measures (items) that are in status "6.3-Production" that were completed between 6/3 - 6/9- I need this number to be populated into a table I created on sheet 2.

    The range of the columns for status is A2:A5181
    The range of date completed is C2:C5181

    Any assistance is greatly appreciated! *I doubt anyone will see this immediately, but if possible, I need this today :) )

  47. 1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    11 2
    1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    11 2
    1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    Fist Colum count only 1, 11 number of count and second Colum 1 number of count please h

  48. Hi,

    I am trying to calculate a formula that encompasses more that one cell as a criteria(so I don't have to copy the formula 4 times which each cell.

    eg I have list of criteria
    DASHBORD
    A53 England
    A54 America
    A55 Scotland
    A56 Australia

    My formula currently is:
    =SUMIFS('Master List'!H:H,'Master List'!I:I,"Ticket",'Master List'!BN:BN,Dashboard!A53)

    How do I change Dashboard!A53 to include A54, A55 and A56 eg(A54:A56)

  49. We list our jobs on a WIP (work in progress) worksheet. Column E is the total revenue expected on the job. Column K is our actual cost in dollars of the job. Column P is the % of the job completed based on Cost/Revenue. Column U is the amount invoiced to date. Column X needs to tell us, based on the % complete, less previously invoiced amount, what our projected revenue will be. The part I can't seem to get is how do we tell Column X to only calculate up to 100% of Column P?

    If costs exceed 100%, it keeps calculating, telling us our projections need to invoice more then what the expected revenue will be. (Obviously we cant charge the customer more just because we didn't control our costs and keep them under 100%. I need it to only calculate up to 100%.

    E: Total Revenue for the job(we enter this amt)
    K: Job Costs in Dollar Amount (we enter this amt)
    P: Calculated % of Work Complete using the formula cost/revenue
    U: Amount invoiced to date (a formula calculating previously invoice + current invoicing)
    X: should tell us what our projected revenue is but only up to 100% in column P. Should column P go over 100%, we don't want to use the overage.

  50. I have a sheet that shows planned and actual delivered tonnage for a product.

    In the A column I have the delivery date, and in the B column, I have the month number ranging from 1 to 12 based on the date in the A column; in the I column, I have the planned tonnage, and in the J column, I have the actual delivered tonnage. The cells in the J column is blank if no delivery have been done yet.

    What I am looking for is a way to make a prognosis calculation based on what has actually been delivered so far this month plus the planned tonnage for the deliveries remaining this month.

    Right now, I have a hidden column that either shows the corresponding J cell value if it is bigger than 0 and if not, shows the I value. And then does a summation of this hidden column where the B value corresponds to the month number I am calculating.

    Is this possible using functions in Excel?

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