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

  1. I have created a weekly revenue record of a hospital spread over a month. I created it such that each sheet contains 3 month record by-weekly. Now my sheet 2 is supposed to be a summary sheet categorizing the income into the various hospital unit . I have used the SUMIFS Formula, it worked but could not place the revenue in its appropriate category.e.g revenue from pharmacy should go to pharmacy in the summary sheet while Revenue from laboratory should go to laboratory.but it is not so with what I created.I believe something is not right with my formula. What could be the problem and please can you help me out?
    Thanks

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. I recommend paying attention to the SUMIFS function. You can find the examples and detailed instructions in the article above.

  2. hai thanks for this page, i can build 2 criteria and with 3 criteria.

    with 2 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021")

    with 3 criteria
    =SUMIFS($G$18:$G$501;$D$18:$D$501;"Apple";$F$18:$F$501;"23/06/2021";$B$18:$B$501;"city")

  3. in sumif how we can get average no.

  4. Thank you for these great explanations and clear examples. They helped me automate some manual things I have done over the past few years, and will help me reduce errors. Thanks!

  5. Thank you for the formula and explanation of SUMIF(range;{array};range) to create an array of results and SUM(SUMIF(range;{array};range)) to sum the array of results into one cell! It helped me very much.

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

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

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

  9. Hi Alexander,

    I need your help with summarizing my data.
    I have data about 162 countries from 1970 to 2013. In each year, I have data about the frequency of s specific ethnic group. There are different ethnic groups for each country, and I'm trying to summarize for each country the data of all the frequencies in a given decade. I can't just roll the command of "sum" to all countries because they are all different in the number of ethnic group cells.
    What can I do?

    It looks like this:
    country year ethnic group freq SUM
    US 1970 muslim number SUMMARY for 1970
    US 1970 jewish number
    US 1970 romancatholic number
    US 1970 non.reli number
    US 1970 orthodox number
    Canada 1970 hindu number
    Canada 1970 sikh number
    Canada 1970 protestant number
    Canada 1970 buddhist number
    Canada 1970 jewish number
    Canada 1970 Muslim number
    Canada 1970 non.reli number

    1. Hello!
      Here is the article that may be helpful to you: Vlookup multiple matches in Excel with one or more criteria.
      I hope this article will be helpful. Unfortunately, you did not explain exactly which data you want to summarize. Your task is not very clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  10. great blog post

  11. Hi Alexander,

    I need help with a 2D sumifs function... My firm has had employees tract their time details in a single spreadsheet.... Column A is the start time (standard dates in 6 minute increments starting from "8:00:00 AM"), column B is the stop time, column c is the project on 1/1, column d is comments on 1/1, column e is the project on 2/2, column f is 2/2 comments etc.

    I would like to write a single formula in a summary sheet where I can reference each employees timesheet, and list each project in column a and each date in row 1, and summarize the data in the resulting table. his would be a simple copy and paste formula if the comments weren't there, but i don't want to delete all those extra columns for every employee. I have seen articles with a 2D sumif, but the index(match() is always used on the sum and never on the criteria.

    Thanks.

  12. how can apply and (&) statement ( to sum different cells with multiple condition in SUMIFS function

    1. Hi,
      The syntax for the SUMIF function does not include &. If you explain your problem in more detail, I will try to help you.

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

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

  15. I want to add sumifs 12 times( for calculation for 12 months). 3 additions are resulting correctly after that results are not as desired

    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. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you

  16. I want to add cell starting with 2 common letters. How can this be added to the formula.
    Like
    FL20.3 -- 40
    FL30.1 --100
    BL12.4 -- 10
    TC10.001 -- 5
    I want to add all FL20.3 and FL30.1. Is it possible?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

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

  18. hi , iam traying to sum the time for user level in multiform time , date and user id
    below is the example
    PTW_USER_ID PTW_DATE PTW_TIME
    58169 24-Jul-20 7:25:51
    155129 24-Jul-20 7:28:45
    58169 24-Jul-20 7:16:08
    30124 24-Jul-20 7:18:04
    30124 24-Jul-20 7:47:46
    58169 24-Jul-20 7:12:10
    111885 24-Jul-20 6:20:19
    111885 25-Jul-20 0:05:55
    111885 25-Jul-20 0:07:16
    111730 25-Jul-20 0:44:54
    111885 25-Jul-20 0:05:55
    111885 25-Jul-20 0:07:16
    111730 25-Jul-20 0:44:54
    111020 26-Jul-20 0:01:57
    111885 26-Jul-20 0:01:05
    111020 26-Jul-20 0:28:10

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  19. I'm trying to come up with a way to calculate total time, by code, by date.
    Example: how much time was coded, on the 21st for the K90 code?
    Date Code Time
    7/19/2020 NU0 4.18
    7/21/2020 K90 5.33
    7/21/2020 K90 0.5
    7/21/2020 E42 2.67
    7/20/2020 E42 4.92
    7/20/2020 K90 8.48
    Thank you for any help

    1. Hello Mike!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =SUMIFS(D50:D55,B50:B55,DATE(2020,7,21),C50:C55,"K90")

      I hope it’ll be helpful.

  20. As a general comment on SUMIFS; note that if none of the criteria are met, the result is zero, not an error of some kind.

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

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

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

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

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

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

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

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

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

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

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

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

  34. Thanks Svetlana from Indonesia

  35. Thank you

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

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

  38. Thanks

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

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

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

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

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

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

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

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

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

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

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

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

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