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

  1. I have received 25 items and want to deliver 5 person. Can you please help to set the formula.

  2. sir,
    I am using sumif formula to sum 3 columns data, but it giving 1column summary.
    syntax is:SUMIF(G3:G619,H621,C3:E619).
    pl advice , how to correct it.

  3. Hello, Please help and thank you in advance!!

    I have a formula to sum values "TimeSheet!E:E" according to month selected "AC2" and by ID & Emp, but what I have been struggling with is the other part of the formula if a month is not selected and a quarter is selected.
    Instead of months as text, I use 1-12 to simplify formula. This is the formula that I am using.

    =SUM((MONTH(TimeSheet!$G:$G)=$AC$2)*(TimeSheet!$B:$B=$B5)*(TimeSheet!$J:$J=E$2)**(TimeSheet!$E:$E))

    AC2 (Month 1-12/1Qtr, 2Qtr, 3Qtr, 4Qtr) B:B (Emp Column) E:E (Miles Column) G:G (Dates Column) J:J (ID Column)

    The formula I have been trying is:

    SUM(SUMIF(AC2,{"1","2","3","4","5","6","7","8","9","10","11","12"},TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,(MONTH(TimeSheet!$G:$G)=$AC$2,TimeSheet!$E:$E),SUMPRODUCT((ROUNDUP(MONTH(TimeSheet!$G:$G)/3,0)=AC2,YEAR(TimeSheet!$G:$G)=AC2,TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,TimeSheet!$E:$E))

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. What exactly do you indicate in cell AC2 (month number or quarter number or date)? What data is stored in column G (date or month number)? Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  4. How do I calculate the total sum in a column which is attached too separate conditions in two different columns?

    Eg: Sum column D if any of the column A (is>700 and 700 and <1400).

    Which formula will work for this scenario?

    1. Hello Akash!
      Read carefully the article above. Use a formula like this

      =SUMIFS(D1:D10,A1:A10,">700")

      For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.

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

  6. HOW TO AVOID TOTALS TO CHANGE IN SUMIFS FORMULAS WHEN YOU FILTER THE DATA

  7. SUMIF and SUMIFS can handle ranges, but not arrays. This means you can't use other functions like YEAR on the criteria range, since the result is an array. If you need this functionality, use the SUMPRODUCT function .

  8. amount days value
    100000 215 1767 (formala =a*d*3/36500)
    i want calculate amount 100000 calculate only one lack

  9. Hi there
    I'm trying to sum info in a slightly different format. If in your first example if the supplier names (John, Mike,Pete) were in cells B1-D1 and all the sales were in B2-D9 under their names, I'm wanting to get the total number of fruit from each supplier on a separate sheet. ie Sumif John and Apples.
    It should be an easy solution, but I'm not getting it - can you please assist?

  10. Hello,

    I am trying to do a sum function in for the number of rows depending on the number of month we are in. For example columns C through N are for the months Jan - Dec. On a separate Cell number 5 for "May". I want to be able to add columns C through G for Jan - May. Can this be done?

  11. I have a datasheet where regular pay is column K and overtime hours is column L. I was told to find How much regular pay and overtime pay did they pay to each individual employee by Calculating the amounts for regular and overtime pay separately using the SUMIF or SUMIFS function. Each employee has multiple rows. How would I set up this formula?

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

  13. Hello,

    Can anyone help me on the formula which is partially working..
    =SUM(SUMIFS('ETP-TFS Data'!$AH$3:$AH$136,'ETP-TFS Data'!$F$3:$F$136,{"72101290","72101210"},'ETP-TFS Data'!$B$3:$B$136,">=1/10/2019",'ETP-TFS Data'!$B$3:$B$136,"<31/10/2019"))
    This formula is picking only first row withing Oct 2019 month, while the other rows are ignored.
    Look forward to solution.
    Archer

  14. Thanks Svetlana from Indonesia

  15. Thank you

  16. I have 2 spreadsheets with several variables that I need to sum.
    Spreadsheet 1
    Column A Employee number
    Column B date range from
    Column C date range 2
    Spreadsheet 2
    Column A Employee number
    Column B Payment date
    Column C Payment amount

    So basically I want to sum all amounts paid to an employee where payment date on spreadsheet 2 falls on the date range on spreadsheet 1.

  17. I want to calculate three different types of matching with consolidate sumif calculations. EX. "date", need to match, "Company Name" should match and "Name"

  18. Thanks

  19. Hi I am using this formaula =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9)). What I would like to do is create a name for the {"John","Mike","Pete"}. Trouble is when I put the name into the formula it represents it like this "{"John","Mike","Pete"}" and the formula fails. How do I stop it text qualifying. Thanks

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

  21. Hi, I am looking for a more compressed way to write this formula.
    I have three different Criteria ranges and three different Sum ranges for each criteria.

    The formula is copied down from row to row and the criteria changes with each move but the criteria range and the sum range must be fixed, requiring the use of fixed and relational references. This was coppied from cell AF6.

    =IF(AK6+(2*(SUMIF($F$6:$F$48,AG6,$A$6:$A$49) + SUMIF($L$6:$L$48,AG6,$M$6:$M$49) + SUMIF($S$6:$S$48,AG6,$N$6:$N$48)))=0,"",AK6+(2*(SUMIF($F$6:$F$48,AG6,$A$6:$A$49) + SUMIF($L$6:$L$48,AG6,$M$6:$M$49) + SUMIF($S$6:$S$48,AG6,$N$6:$N$48))))

  22. Excel would evaluate the farmola =20*10/5*8 return answer

  23. how is the solution if I need 3 criteria and 3 sum like the example I made by combining with & which is actually wrong, but I don't understand how to combine them so that the desired results are achieved: = (SUMIF ($ B5, P $ 3, $ E5: $ I5 )) & (SUMIF ($ C5, Q $ 3, $ J5: $ L5)) & (SUMIF ($ D5, R $ 3, $ M5: $ N5)) and the results are not as I expected, please take a solution, Thank you

  24. Hi, I am wanting to count the number of set (text) entries on certain dates: today / last 7days / last 30days.

    I.e - how many “client defers” in column C do I have recorded today / last 7 / last 30 - date recorded in column B, would love someone to help. Thanks

  25. I need to know how many times person × shows a specific ID# and thier costs. Any suggestions? I've gotten it to work with the person # and costs but I'm not sure how to add in their specific ID# and carry it down to the rest of the group. Can I put multiple ID#'s in parenthesis so it finds the one that has that person # and that specific ID#?

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

  27. How could I sum two ranges: C20:I20 and C22:I22 using two Criteria ranges: C19:I19 and C21:I21 ... If if any of the cells in the criteria ranges have the criteria "PTO"?

    I have tried a few these, but have not been successful:
    =SUMIFS(C20:I20, C19:I19,"PTO”) + SUMIFS(C22:I22, C21:I21,”PTO")
    =SUMIFS(C20:I20, C19:I19,"PTO”,[C22:I22, C21:I21])
    Essentially, I have this working formulas but need to add another Sum_range and Criteria_range to the formula.

    Thanks in advance!

  28. I want to make the criteria as dynamic by getting the input from user via slicer. So I tried using sum before sumifs and if I hard code the multiple criteria on the same column it works =sum(sumifs(sum_range, criteria range, {"a","b"}) but if I want to get the input from user via slicer which reference to a cell it doesn't work. =Sum(sumifs(sum_range, criteria range, name_manager).
    Name manager to a input which I receive it from user via slicer.

  29. Hi,
    can it be used for the Criteria Range the full column for example as I cant get the formula with it. I cant have the exact range as the range is changing from day to day and there is 29000 lines.

  30. No. of months Amount
    12 14
    20 14
    12 9
    15 18
    25 14
    68 18
    70 9

    Hi Svetlana,
    Could you please help me on following,
    What is the formula for If i want to calculate total amount in(Column 2) for 12 to 25 months (Column 1) ?
    I am greatly appreciate if you could help me on this. Thank you

  31. My congratulations for this excellent article. I already use some criteria for my company.
    What I haven't understood is the following: In the formula
    =SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
    the MATCH syntax should say 'Take the value H1:H3, put it to C2:C9, with exact match, eg
    --(ISNUMBER(MATCH(H1:H3, C2:C9,0))), D2:D9)
    But this gives an error. Why?
    Again congratulations for your really excellent article!

  32. I ready type sheet. Some problem formula. Which Lesson 1. Help me

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

  34. This log has really helped me finding something valuable & usefull, which I may not aware about.

    Thanks!!!!

  35. I'm trying to figure out a way to make something like this work but I am unable to do so. Please, will someone help me?
    A B C
    1 Amounts Condition Result
    2 amount 1 if C1=text and B2=empty cell, enter A2; if C1=text and B2=not empty cell, enter A2; if C1=numbers and B2=empty cell enter C1+A2; if C1=numbers and B2=not empty cell enter C1-A2;
    3 amount 2 if C2=text and B3=empty cell, enter A3; if C2=text and B3=not empty cell, enter A3; if C2=numbers and B3=empty cell enter C2+A3; if C2=numbers and B3=not empty cell enter C2-A3;
    4 amount 2 * if C3=text and B4=empty cell, enter A4; if C3=text and B4=not empty cell, enter A4; if C3=numbers and B4=empty cell enter C3+A4; if C3=numbers and B4=not empty cell enter C3-A4;
    5 amount 3 h if C4=text and B5=empty cell, enter A5; if C4=text and B5=not empty cell, enter A5; if C4=numbers and B5=empty cell enter C4+A5; if C4=numbers and B5=not empty cell enter C4-A5;
    6 amount 4 if C5=text and B6=empty cell, enter A6; if C5=text and B6=not empty cell, enter A6; if C5=numbers and B6=empty cell enter C5+A6; if C5=numbers and B6=not empty cell enter C5-A6;
    7 amount 5 6 if C6=text and B7=empty cell, enter A7; if C6=text and B7=not empty cell, enter A7; if C6=numbers and B7=empty cell enter C6+A7; if C6=numbers and B7=not empty cell enter C6-A7;

    So the end result would look and work like this:
    A B C
    1 Amounts Condition Result
    2 10 10
    3 15 25
    4 20 * 25
    5 25 yes 25
    6 30 55
    7 35 6 55

  36. MR Severity Category Ageing in Days
    Bhavnagar NSA Logical Alarm-OMC >3 Days Feedback
    Surat SA Physical Alarm - BSS >3 Days OMC Logical
    Surat SA Physical Alarm - BSS >3 Days OMC Logical
    Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
    Ahmedabad SA Physical Alarm - BSS >3 Days RING SPLITTING
    Rajkot NSA Logical Alarm-OMC >3 Days OMC Logical
    Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
    Ahmedabad SA Physical Alarm - BSS >3 Days TXPL
    Baroda SA FAN-Alarm >3 Days FAN FAILURE
    Rajkot NSA Logical Alarm-OMC >3 Days wip
    Rajkot SA Physical Alarm - BSS >3 Days wip
    Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
    Ahmedabad NSA Logical Alarm-OMC >3 Days TCTS
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    Baroda NSA Logical Alarm-OMC >3 Days VBS_LL services
    Baroda NSA Physical Alarm - BSS >3 Days VBS_LL services
    I am using Sumifs formulea for this attached ment in 2nd Sheet and getting corrected summation pls help to resolved this for 2nd sheet

  37. hi,
    i have a list of value is about to 100. i want to know the 6 values which will give a sum of a particular value,
    ex:
    52636
    6326
    63699
    12563
    25699
    1236
    15693
    i want to know 6 values in a above values which give total of 165289.
    pls give a solution,
    thankyoy

  38. Please help me how to use =sumifs formula to fund the sums more thatn 4 crieteria ranges.
    Thanks

  39. Hi,

    Can you please help, trying to sum if using multiple criteria including a date range. However it is returning 0.00 value. The date range seems to be the issue as when I remove this the other criteria works. Going around in circles and not sure what I am doing wring.

    =SUMIFS('Detail Transaction Listing'!$J:$J,'Detail Transaction Listing'!$D:$D,$B$2,'Detail Transaction Listing'!$A:$A,B15,'Detail Transaction Listing'!$H:$H,">="&Sheet1!$B$5,'Detail Transaction Listing'!$H:$H,"
    Sheet 1 B5 & C5 is the criteria field.

    Really appreciate your help/

  40. I hoped to have finally found the solution, but it regrettably failed.

    {=SUM(($B$38:$B$275=B27)*($C$38:$C$275=C27)*($D$38:$D$275=D27)*($H$38:$H$275))}
    In this formula B27, C27 and D27 are the criteria. It works well, ONLY when all criteria cells (B27, C27 and D27) are populated

    I was hoping that if one or two of the criteria cells is empty, it would still add $H$38:$H$275 based on whatever criteria (B27 and/or C27 and/or D27) is in place. Instead, the result I'm getting is 0 if one of the criteria is empty.

    Any ideas how to solve this?

  41. Lot of thanks to to you. I got the perfect formula at the right time. It saves me lots of time. Great.

  42. =SUMIFS(H2:H1000, D2:D1000,"vehari", G2:G1000, "short")

    I want to calculate a range...when two conditions are ture..

    But this formula is not working.

    Kindly help.

  43. How can I get a count of the number of rows returned using the sumifs function?
    My current function is
    =SUMIFS $B$2:$B$105,$C$2:$C$105,O111,$D$2:$D$105,O115,$E$2:$E$105,O113)
    and I would like to know how many rows were returned to get my total that was returned with the sumifs function.
    Thanks,
    Matt

  44. This tutorial was a massive help!
    Easy to understand and follow, and it works!:)
    Thanks so much for providing this for the public!

  45. Hi,I need SUMIFS formula with multiple columns reference.column1-type, column2-length adress, column3- lenghcount. now need total count value with each length adress(column2)

  46. Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can someone please help

  47. Hi, I am trying to add sum of all outstanding payment of each customer in their respective cell who has crossed the due date but having difficulty in implementing as both criteria has to be checked customer name and the date of supply.

    can some please help

  48. Sorry people, read cell A2 as follows

    =sumif(criteria range,"Apple",sum range)

  49. Hey people can you tell us how to use sumif for the following;

    Criteria 1 - Apple
    Criteria 2 -Orange
    Criteria 3 -Lemon
    Criteria 4 -Pineapple

    we want to use sum if to total the sum of "Apples" And all others in s single cell.

    eg:

    In Cell A1
    =sumif(criteria range,"Apple",sum range)

    In Cell A2
    =sumif(criteria range,"Apple",sum range)

    this above formula is not working??? to be specific it sometimes work

    also tell us can we apply this formula across sheets in the same excel work book?

    awaiting your wise response....

  50. I am trying to make work easier for my team by building a formula that adds up data based on two criteria: The month (this is okay). Each month has for to five weeks. I want the formula to add data in each months for all weeks in the month but up to the current week. I did the formula below. It however appears that Excel does not recognise the last argument below which uses reference to a cell:

    =SUMIFS('Weekly targets'!$F$14:$BE$14,'Weekly targets'!$F$1:$BE$1,'Summary by Product'!$E$3,'Weekly targets'!$F$3:$BE$3,"<=$C$4")

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