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

  1. Hello Ablebits team!

    I have an interesting one for you. Can we make the sum_range dependent upon criteria in another cell? Let's say I have this formula:

    =SUMIFS(Base!$AF$4:$AF$2762,Base!$O$4:$O$2762,$B9)

    But I want to be able to change the $AF based on a value in another cell (Let's assume cell D6), so that when I update the value in D6 to AG, the cell begins summing information in Column AG instead of Column AF. Is this possible with a formula, or do I have to update the sum_range columns every time I update?

    Большое спасибо!

  2. Hi,

    This is Darshan.
    In example one. Can we mix with criteria rang also, as below.

    > Original >> SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],...)

    > Amended >> SUMIFS(sum_range, criteria_range1, criteria from A1:A20,[criteria_range2, criteria2],...)

    I hope this explain.

    Thank you,
    Darshan

  3. if in criteria i used less than sign like <60 and if i want to get output from range who is matching with <60 then how i use sumifs formula to get that
    please find below example for more details on it,

    Age Amount
    <60 2500.00

    i m using formula as =sumifs(Range:criteria,criteria) so what is wrong in it

  4. Hi,

    I have a situation similar to "Example 2. SUM & SUMIF with an array argument" under "Using Excel SUMIF with multiple OR criteria".

    But, the array is specified in a different cell i.e. the criteria {"John","Mike","Pete"} is given in a different cell. How do I reference that cell with criteria in the sumif statement.

    Thanks.

  5. Hi,

    I want to include "or" condition in sumifs in two different columns. How can that be done?
    Ex: Col1 Group : "Primary","Seconday" and Col2 Subjects : "Maths", "Science", "History".

    1. Name Group Subject Marks
      Ashna Primary English 50
      Ashna Primary Maths 70
      Ashna Primary Science 45
      Ashna Primary History 55
      Ashna Secondary English 75
      Ashna Secondary Maths 68
      Ashna Secondary Science 89
      Ashna Secondary History 98
      Kirti University Science 56
      Kirti University Spain 63
      Kirti University History 52
      Kirti Secondary Science 87
      Kirti Secondary History 36
      Kirti Secondary Maths 49
      Archana Secondary History 126
      Mansi Primary Maths 89
      Sarika Secondary Science 90

      Out of this, for every name, marks should be added where group is Primary / Secondary and Subject Maths / Science /History

  6. Hai,

    I want to total of three sum range using below formula in sumif function.Please suggest. I do not want to use three formula of Sumif+Sumif+Sumif.

    =SUMIF($D$7:$D$18,D4,$E$7:$G$18)

    where D7:D18 is range , D4 is criteria and E7:G18 is sum range.

  7. Hi,

    I want to use the SUMIFS function for the forllowing table

    Sheet1
    Column A has name of Customers Column B, C & D has year 2014, 2015 & 2016

    Sheet 2
    Column A Customer Name
    Column B Year
    Column C Months
    Column D Revenue from Customer - Product A
    Column E Revenue from Customer - Product B
    Column F Revenue from Customer - Product C
    Column G Revenue from Customer - Product D
    Column H Revenue from Customer - Product E

    I prepared the table in sheet 1 as mentioned above, but with sumifs i can create only one table for revenue from customer - product A

    If I want to change the table showing result as 'Revenue from Customer Product - B' I need to change he the details of sum range in sumifs formula.

    Can you give me a formula or a logic with whcih I can just change the reference formula get changed as per the reference in that cell.

    Thanks in Advance.
    Anand J

  8. THANKS!!

  9. Hello I wants formula for
    If I wants to sum B2 shell with minimum number shell in the range of A1:A6

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

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

  12. I am having trouble with text. My formula is =SUMIFS(YTD!$H:$H,YTD!$E:$E,">=1/1/2017",YTD!$E:$E,"<=1/31/2017",YTD!$G:$G,"Jason")
    I want to know how many deals Jason did in January, but it's only picking up some of them, and not every time his name comes up. Any tips?

  13. in sumif formula =SUMIF(ZDDR!$G$5:$G$1456,A5,ZDDR!$H$5:$H$1456)
    it automatically calculates what is in A5 matches with the ranges

    but when i use sumifs formula

    =SUMIFS(ZDDR!$H$5:$H$1456,ZDDR!$B$5:$B$1456,"=B4",ZDDR!$G$5:$G$1456,"=A5")

    it will not give any answer. My problem is i want to use cell number insted of text how is it possible.

  14. Hi Svetlana, could i please get some help with this one

    My previous formula is working, but we have had a change of data from another department, so i am hoping to get an outcome from the following

    Column AF has a total figure of $, in that total, they are either amounts of $135 or $180, but the spreadsheet that i am importing into my spreadsheet doesn't specify that amount that the total is derived from.

    my previous formula was

    =SUM(SUMIFS(AF11:AF5000,I11:I5000,{"BSB30115"},AK11:AK5000,{"organisation name"}))

    I would like two formulas as they need to go into two separate cells, so from Column AF, i need a formula that uses that column and works out the total amount, whether it is 2 amounts of $360 (these amounts can be as high as $2520 in total), 1 amount of $180 and then the other one being the same type of formula but the amount of $135.

    so the return values would be in two cells, one being for the $180 amounts (total) and then one for $135 amounts (total)

    so my end result would be splitting the one column to have two different totals for each of the criteria, hope this makes sense,

    Thank you in advance

  15. Thank you very much for the tutorial. It really helped me.

  16. the formula that I am using is not showing up in the previous 2 posts, when I try copy and paste the formula. SUMIFS($Q$2:$Q$3208, $F$2:$F$3208, $R20, $G$2:$G$3208, ">=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

  17. Hello,
    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    Thank you,
    jimmy

  18. Hello,
    This is driving me nuts. Any help is appreciated. I wish to sum numbers in Col Q, if text in Col F matches matches text value in R20 AND dates in Col G >=9/1/2015 and Col H=9/1/2015", $H$2:$H$3208, "<=9/31/2015")

    I need to sum

  19. Dear Experts,

    Kindly help below mentioned query...use sumif function - account code is unique data, but i need sum if month should be matching ....

    Month AccountCode Amount
    Aug-16 21011077 4290
    Sep-16 21011077 4070
    Oct-16 21011077 5500
    Nov-16 21011077 5500
    Dec-16 21011077 4730
    Jan-17 21011077 5230
    Feb-17 21011077 5990

    A.Code Description Aug-16 Sep-16
    21011077 Visa Letter Fee

    Unique Data is Account code, but sum is arrive based on month.

    Thanks in Advance...
    Ahmed Khan

    1. Dear Ahmed,

      You can try this formula.

      =SUM(SUMIFS(amount_range,account_code_range,"21011077",month_range,{"Aug-16","sep-16"}))

      Hope this helps.

  20. I need a formula for a profit and loss sheet I am creating. I need to add a column of cells but only those that are listed as a specific month ie 'January', 'Feb' etc.
    Then I need these January amounts to be bought back into another sheet and for them to match in the corresponding cell.

    Example Sheet 1 'Expenses Entry'-
    Column heading in Column B'Month'- January/Feb/ April/ Dec etc
    Column heading in Column D 'Description'- Bank Charges/ Food/ Tools etc
    Column Heading in Column F 'Amount'- $40.00/ $55.00 etc ( these are the amounts I need to be added up if they all fall under January, then I wil copy the formula for Feb/ March/ April etc

    Sheet 2- 'Profit and Loss
    Column A is for all the descriptions- Bank Charges/Food/Tools etc
    Column C is where I want the sum of all January's Bank Charges to be or whatever the name of the 'Description' is

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

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

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

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

  25. i need to devolop sum if for tow condition

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

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

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

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

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

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

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

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

  33. thanks

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

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

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

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

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

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

  40. please see attached

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

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

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

  44. Thanks

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

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

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

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

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

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

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