Comments on: SUMIFS in Google Sheets to sum cells with multiple criteria (AND / OR logic)

Google Spreadsheets provide two functions to conditionally sum cells, SUMIF and SUMIFS. Regular visitors of our blog are already familiar with the SUMIF function that we covered last week, and today we are going to have a closer look at its plural version. Continue reading →

Comments page 4. Total comments: 181

  1. Dear ablebits.com Team,
    How to use sumifs () function between multiple Google sheet files ?!
    I want a file just to display the results from the data of other files!
    For example: File A will use SUMIFS functions to add data from files B, C, D ...
    Please help me!

  2. Thanks for this great explanation!
    I think this is close to what I need; however, I'm having trouble with a nested quantity.
    I have 3 sheets: Responses (Static Data), List (Static Data), Compiled (Static with Dynamic Values/formulas)
    All columns are ranges with the respective names.
    Responses (Static) contains the following "columns/ranges": RespDate, RespItem, RespQty
    All Responses data could be duplicate entries.
    List (Static) contains: ListItem, ListWeight
    All ListItem entries are unique
    Compiled (Static with Dynamic) contains: CompDate (Static), CompWeightTotal (Dynamic)
    All CompDate entries are unique
    I want to display the total weight of all items (CompWeightTotal) for each specific date (CompDate) in the Compiled Sheet.
    Step 1: Get the Sum of RespQty of each specific item for that specific date.
    Step 2: Multiply the sum of the Qty for that item for that specific date by the ListWeight.
    Step 3: Get the Sum of all weights for that specific date.

    I have part of Step 1; however, it is a general sum of RespQty: SUMIF(RespDate,A2,RespQty) where "A2" is a date. If 2 entries exist for this date and each entry had RespQty of 2, the returned value is 4.
    I need to figure out how to count the quantities of a specific item and then multiply this sum to the weight of the specific item found in "List".

    Maybe I should be using an array formula?

  3. Has google changed the way SUMIF works? Sheets now says the order of operators is (Range,Criteria,CriteriaRange) which is different from what's described here, and Sheets also says that it can only take three operators, so it won't accept a Criteria2 and CriteriaRange2.

    1. Hello there,

      This article explains SUMIFS, while SUMIF is described here. They both work as before. Please make sure you're trying to use the correct function. Also, check your File > Spreadsheet settings, and set a locale if it's missing.
      You can find the description of the functions on the Google help page as well:
      SUMIF - https://support.google.com/docs/answer/3093583?hl=en&ref_topic=3105474
      SUMIFS - https://support.google.com/docs/answer/3238496?hl=en

  4. Hi All!
    How can I do a NOT statement...
    =SUMIFS( Range, Criteria Range, NOT "A")

    Thanks! (Example please?) . ;-)
    Robert

  5. Can I use this to count checkboxes?
    If so, what is the function?

    1. To count checkboxes, you need to use the COUNTIF function. You can learn more about it here.

      Since Google sees checkboxes as TRUE (checked) or FALSE (unchecked), your formula should be like this:
      =COUNTIF(A2:A10,TRUE)
      where A2:A10 is the range with checkboxes and you count those that are checked. To count those unchecked, replace TRUE with FALSE.

  6. Hi guys i only want to know how to turn the result to "0" if one values is = "0"
    For Example A1 = 500 and A2 = 0 i want to sum them but i want the result to turn "0" because A2 has no value indicated. can you show me what formula should i use.. pls. help im only new to this spreadsheet thing.

    Thank you very much in advance.
    and God Bless.

    1. Hi Harley,

      I believe SUMIF is not enough since the logic of your calculation contains two conditions that lead to different results - one for when A2 is 0, and the other when it's not. Thus, I'd recommend trying this formula:
      =IF(COUNTIF(A1:A10,"=0"),0,SUM(A1:A10))

      I indicated the range A1:A10 just for this example, but you can adjust it according to your data.

      You can learn more about the IF function here.

  7. How would I create my or argument if the arguments were numbers? For instance, what if you wanted to sum all of the orders that came to a certain dollar amount? This would make a task at work a lot easier.

    1. Carlos,

      I'm sorry but your question is not entirely clear.
      Could you provide an example and explain the task in detail? If so, feel free to share your sample spreadsheet with support@4-bits.com with the data you have and the result you'd like to get. Please include the link to this comment as well.
      I'll look into your task and see what I can do.

  8. Hi, I have a sheet with Column A consisting of Dates, Column B consisting of Mango, Banana, Pineapple, Blueberry and Column C of their respective Prices. My query is, to sum all the fruits right from the first date till the latest date but want to exclude only Mango price right from first date except the latest date. Any help is appreciated. Thanks in advance!

    1. Hi Ravinder,

      I'm sorry but the part with Mango is not really clear. For us to understand your task better, feel free to share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. Just make sure to shorten the table to 10-20 rows and provide the link to this comment as well.
      Thank you.

  9. Hi. I have sheet with a drop down cell with three ranges ( RW -rework, WD - warehouse damages and Re-pack). In one cell I have a formula to count the value of RW in one day. I was asked to ad in the drop down cell the range Re-pack to be added on the value of rework. I trayed to ad this range " Re-pack" in the formula but is not working. The formula is : =SUMIF(C5:C,ʺWDʺ,G5:G)and I would like to ad in this formula the range "Re-pack" to be counted in the same cell where is added the value of "RW".

  10. Amazing, Thank you

  11. I have my data base in columns, and one of them is showing quarters as (1, 2, 3, 4).
    in my lookup sheet, i created an additional periods as a drop down menu (1,2,H1,3,Q3YTD,4,H2, TY)...
    In the same Sumifs, everything works well with 1, 2, 3, 4, --- but i need a way where when i select H1, it can add both 1 and 2... etc...

    can anyone help.

  12. what formula do I use when taking two numbers from different cells and have them equal complete sets. Example 20 RH, 55 Lh = 20 complete sets.

    1. Thank you for contacting us, Virginia.
      I'm sorry but your task is not entirely clear. If you could describe it a bit more, we may be able to advise the solution.
      Thank you.

  13. I have a sumifs formula working right now for a running total. I now want to use the same formula to only calculate the amounts over or under 0, but do not know how to add this into the criteria. Can anyone help? The formula I am using that is working for the total is this..
    =sumifs('2019 Balance Sheet'!E:E,'2019 Balance Sheet'!B:B,"Saver",'2019 Balance Sheet'!C:C,"Car",'2019 Balance Sheet'!D:D,"Fuel")
    How do I then total only the amounts greater than 0 to track deposits in another cell, and only amounts less than 0 in another cell for withdrawals.

    1. Thank you for your comment, Lara,

      You simply need to add a couple of more arguments: the range and the additional condition: '2019 Balance Sheet'!E:E,"<0"
      So the entire formula will look like this
      =SUMIFS('2019 Balance Sheet'!E:E,'2019 Balance Sheet'!B:B,"Saver",'2019 Balance Sheet'!C:C,"Car",'2019 Balance Sheet'!D:D,"Fuel",'2019 Balance Sheet'!E:E,"<0")

      For another formula, change the condition "<0" to ">0".

      1. Brilliant! Thanks so much, I was trying something like this but wasn't getting the " & ' correct but knew I was close.

  14. Hi! I'm trying to make a SUMIFS formula to sum up expenses where one of the criteria is date, but i have two columns with dates (Column A - Date Purchased and Column S - Date Paid). Column S only has entries when it is different from Column A, otherwise it is blank.

    So for the criteria range, I want to use column S if it has entries, otherwise I want to use column A.

    My current formula only makes use of Column A for now and I don't know how to go about it. :(

    Can you please help me? Thank you!

  15. Hello! I'm need a help. In de picture 2, why expression
    SUMIFS(B7:B15 , MONTH(C7:C15), "May", a7:e15, "Oranges")
    return #error?
    Thanks.

    1. Hi Cleomir,

      The SUMIFS function does not accept anything but ranges in criteria_range arguments, therefore MONTH(C7:C15) won't work.

      Instead, try using this SUMPRODUCT formula:
      =SUMPRODUCT(--(MONTH(C7:C15)=5)*(A7:A15="Oranges")*(B7:B15))

  16. Despite using the correct formula in Google sheets(or at least i think I am) , I get 0 when the calculation is done, even though I should get a sum. Any idea of why this could happen ?

    1. Hi Irina,

      If you're trying to use "or" logic, Google Sheets's SUMIFS function does not currently support this. It only supports "and" logic - i.e., Google Sheets will only sum the cells that match all of the criteria you enter into the SUMIFS formula.

    2. Hi Irina,

      If you could share your sheet or at least post a formula here, we will try to figure it out.

  17. its not only just in amount and items because i cannot solve it because mine is for department i need to know how can i solve it

    1. Charlene:
      SUMIF can add up cells based on just one condition. If this is what you want, please check out this tutorial: SUMIF in Google Sheets with formula examples. https://www.ablebits.com/office-addins-blog/sumif-google-sheets/
      SUMIFS can sum cells based on two or more conditions. In this tutorial, you fill find a simple explanation of SUMIFS syntax and formula examples for different data types. f this is what you need scroll to the top of this page where you can begin the tutorial.

  18. I want a SUMIF formula about Departments i want an example of that can you give atleast 2?

  19. I wanted to use sumifs in five different sheets with assistance of a drop down list. Any help.

  20. Please help.......
    I want to count the number of individual product an agent sold in a Sunday thru Saturday date range, using two google sheets.
    Sheet1 has each transaction starting by 00/00/00.
    Sheet2 is the summary page starting with the week, agent, each product-- for 5 weeks, then on to the next agent.

  21. I am looking for a formula.
    And the conditions are :
    If B1, C1, D1, E1, F1, G1. all values are >0 ...then H1=400

    If B1, C1, D1, E1, F1, all values are >0 ...then H1=300

    If B1, C1, D1, E1. all values are >0 ...then H1=250

    If B1, C1, D1,. all values are >0 ...then H1=200

    If B1, C1 all values are >0 ...then H1=150

    If B1 is >0 ...then H1=100

    If (B1:G1)= 0 ...then H1 = 0

    1. You are assuming that the numbers are all POSITIVE and in non-ascending order from B1 -> G1. That is, if B1 & C1 are positive, but D1 is zero, that E1,F1 and G1 will also be zero. Etc.
      In that case, the following formula will do the job (at H1):
      =choose(countif(B1:G1,">0")+1,0,100,150,200,250,300,400)

  22. I have a list of names in cells from G1:G20 (which can be increased later); I want that when I input one of the names in the list in D1:D500, the result in B1:B500 should read “CIS”.
    Can someone give an excel formula, please for this statement?
    Regards
    Emanuel

    1. Hi Emanuel,

      Enter the below formula in B1, and then copy it up to B500:
      =IF(COUNTIF(G:G, $D1)>0, "CIS", "")

  23. GreetingsMs. Svetlana Cheusheva,
    I'm in kind request of your assistance,
    I am working with stock which i normally issue out weekly.
    i have different columns, opening balance, quantity received, quantity issued, closing balance among others. you realise i have a formula in the 'closing balance' column that automatically calculates; (=opening+received-closing). i have another column 'number of days out of stock'. this column includes the number of days of a month (out of 30 or 31) for which the item had stocked out.
    example: if i opened with 30 items on 1st march, i received 20 on 10th,
    i issued out all the 50 on 21st to remain with 0, then the days out of stock is 31-21=10
    * i issued out all the 50 on 21st to remain with 0, but more is received on 25th then days out of stock will become 25-21=4.
    i need a formula that will automatically read when the 'current stock' column becomes zero and then subtracts to return the days out of stock.
    (i dont mind if the formula i am looking for assumes all months have 30 days).
    *(actually even if its not possible to meet the second condition, i will settle with that)
    thank you in advance

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