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 2. Total comments: 181

  1. Hi! I have a super easy one for you but for some reason I just can't get my array formula to work. I need a list of sum of column C for each combination of Comumn A & Comumn B that currently exists.

    Example data:
    Lindsay Mitchelle 19
    Staci Elvers 9
    Shelia Jacobs 19
    Staci Elvers 17
    Lindsay Mitchelle 18
    Shelia Jacobs 20
    Lindsay Stamon 7

    What I need the results to be is:
    Lindsay Mitchelle 37
    Staci Elvers 26
    Shelia Jacobs 39
    Lindsay Stamon 7

    Here's a sheet with some more example data:
    https://docs.google.com/spreadsheets/d/1GNgV_s_o5gclVGqi02bEKAdZKBz7ZCp8fbJe9_nBkdI/edit?usp=sharing

    1. Hi Kristi,

      Please add the desired result directly to the file. You see, I don't know whether you need it on the same sheet or other certain cells. Also, do you use a list of all names that appear in a column somewhere? If so, please include it as well. Thank you.

  2. Hi goodmorning. Can you help me in formula I'm a beginner on this field.
    this is my formula
    =SUMIFS('Order File'!E:E,"1299"'Order File'!F:F,"GOLD WATCH",'Order File'!D:D,"DELIVERED")
    i need the total for the gold watch that was delivered and the amount is 1299, but there are other gold watch that has other amount. do i need to use "IF" function inside "SUMIFS? thank you

  3. Hey Guyz, can anyone help with this formula?

    =SUMIFS(ImportRange("1j_7JWjcC9I5I_erUOCj0TnVKn7mvveBdPuy89pWKJb8",'Master Deal Data'!$C$2:$C), ImportRange("1j_7JWjcC9I5I_erUOCj0TnVKn7mvveBdPuy89pWKJb8",'Master Deal Data'!$BG$2:$BG),$B3, ImportRange("1j_7JWjcC9I5I_erUOCj0TnVKn7mvveBdPuy89pWKJb8",'Master Deal Data'!$BH2:$BH),2021)

    Here $B3 is the month name like 'January'...
    If I try CountIfs, it works perfectly but when I try this formula i.e. SUMIFS it shows me 'Argument must be a range'.
    If i use this formula directly in the sheet with which I am linking using Importrange function, it works too...

    Please anyone help.

  4. How do I use SUMIF to only a select and take the sum of the two smallest/largest/middle values?

  5. Hi I would like to combine these 2 formulas into 1 using SUMIFS or whatever it is I need but I am having a hard time. They both work correctly individually.

    The first formula below looks through a column of hours and sums them only if they fall within range of a month. For example, it will sum only if hours were entered in January 2022. (A1 is the date for example 1/1/2022)

    =sumifs(G7:G805,H7:H805,">="&DATE(YEAR(A1),MONTH(A1),1),H7:H805,"<="&EOMONTH(A1,0))

    The 2nd formula sorts through a column and sums them only if a "SUBJECT TAUGHT" matches. (In my example it sums up only if Pathology was lectured.

    SumIF(I7:I37,"Pathology",G7:G37)

    Basically I am trying to nest or combine them so that it sums up the subject taught only if it falls within a month/date range.

    1. Hi Danny,

      If I understand you correctly, you just need to add the criteria_range & the criterion from the second formula into the first one:
      =sumifs(G7:G805,H7:H805,">="&DATE(YEAR(A1),MONTH(A1),1),H7:H805,"<="&EOMONTH(A1,0),I7:I805,"Pathology")

      However SUMIFS requires all arguments to be of the equal size, so you'll have to extend I7:I37 to I7:I805.

      If this doesn't work for you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Once you share the file, just confirm by replying to this comment.

      I'll look into it.

      1. Hi, I'm having a similar issue. I have two sheets within a document: Sheet 1, "Pedidos" with a list of transactions showing date, quantity and price in the columns; Sheet 2, "Sumario Mensal" with a summary of products sold within the month for all of 2022.

        When I use SUMIFS to calculate quantity "quantidade" sold within a date range, it returns the correct value: =SUMIFS(Pedidos!$C$3:$C,Pedidos!$A$3:$A,">="&F$1,Pedidos!$A$3:$A,"="&F$1,Pedidos!$A$3:$A,"<="&EOMONTH(F$1,0),Pedidos!$B$3:$B,"="&B3)

        However, when I try to combine both formulas Sheets doesn't recognize the second formula as criteria_range 3, criterion. It keeps reading it as criteria_range 2. Have you ever seen anything like this?

        I'll share the Sheet with the email you mentioned and if you guys can help me, it would be awesome!

  6. Hi Natalia,

    i would like to have a formula to SUM a range in column D with criteria in column F (with text "x" or any number), the SUM are from row D2:D6.
    i was try with this formula LOOKUP(1;1/(F2:F10);F2:F10) to find the last filled cell but i can not found how to combine with the SUM formula just to SUM from D2:D6 with said criteria.
    with the criteria i do expect to able to calculate or SUM until any criteria as filled cell in column F. (text or any number)

    A B C D E F
    1 BALANCE PROFIT PROP+BAL WD LAST BALANCE TO SUM
    2 100,00 13,00 113,00 1,30 111,70
    3 111,70 14,52 126,22 1,45 124,77
    4 124,77 16,22 140,99 1,62 139,37
    5 139,37 18,12 157,48 1,81 155,67
    6 155,67 20,24 175,91 2,02 173,89 x
    7 173,89 22,61 196,49 2,26 194,23
    8 194,23 25,25 219,48 2,53 216,96
    9 216,96 28,20 245,16 2,82 242,34
    10 242,34 31,50 273,84 3,15 270,69

    WD up to "x" : 8,21

    i do appreciate i you can share me the formula and many thanks for your kind support.

    salam,
    agus.

    1. Hi Agus,

      Try this formula:
      =SUM(INDIRECT("$D$2:"&CELL("address";INDEX($D$2:$D$10;MATCH(FALSE;($F$2:$F$10="");0)))))

      1. Wow....
        this is really amazing formula, first i put as a normal formula and the result is ERROR with error message #VALUE!.
        i was try to execute every single formula on those but still error then i realize that this is an array formula so i press CTRL-SHIFT-ENTER when executing the formula and it is working fine.

        really really appreciate for quick response and thanks a bunch for the shared formula.

        salam,
        agus.

        1. Agus,

          I'm really sorry, I did forget to mention that it's an array formula! Glad to know you figured it out though – hope it didn't take too long :)

  7. Hi, I am at my wits end. I have been trying to use countifs and sumifs to generate results in a table with no luck. Long story short, My data sheet is A1:K150, Column G has a dollar value (Dollar Amount), Column G, text/number combo (reason code) and Column J is text (Action Taken)
    Example 1 that I could get Reason code is R13, I needed to count the times Reason code R13 was taken as a loss
    Formula used with success is =COUNTIFS($H$3:$H$150,"R13",$J$3:$J$150, "Loss")

    Now what I am trying to do is take the sum of the amount in column g and include it to the same results. Rather than count how many, I would like to sum to total of Column G where Column H is matches the reason code and column J is the action taken. IE Sum of items for R13 taken as loss.

    Any help would be greatly appreciated.

      1. Thank you, I totally missed that. I figured it out after reviewing.

  8. So I did a Sumifs function to add up day pay within the date range of the pay period...easy stuff because it's a basic sum.

    What I would love to do (but can't quite figure it out) if write something similar but to subtract (clock in and clock out times) also within a date range.

    I'm just savy enough to be dangerous...am I wasting my time trying to figure this out? Is it even possible to find a difference in two cells with date range criteria?

    1. Hello Brenda,

      For me to be able to help you, please create a sample spreadsheet with 2 sheets: (1) a sample of your data (2) the result you expect to get; and share it with us – support@apps4gs.com. The result sheet is of great importance and often gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and do my best to help.

  9. Hi! I have a very complex formula (for me) that works in excel, but not Sheets. Can anyone show how to convert this for Sheets? It appears that I need the OR logic for the value arrays in SUMIFS and COUNTIFS, but my childish attempts to solve this have failed.

    The article gives two options for OR logic using the non-S version, but it seems I still need to retain the S version of these functions. Any help is appreciated.

    =IFERROR(SUM(SUMIFS(Sets!H:H,Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))/SUM(COUNTIFS(Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5)),"")

    1. I solved my issue using the formula below. Regexmatch is needed for SUMIFS I guess. Along with some shuffling of the functions.

      =IFERROR(ArrayFormula(SUMIFS(Sets!H:H,Sets!E:E,A9,REGEXMATCH(Sets!C:C,"^(235|364|405|412|416|423|469)$"),TRUE,Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))
      /SUM(ARRAYFORMULA(COUNTIFS(Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))),"")

  10. Hello, In the spreadsheet below, on the first sheet called "Payouts", I am currently using Sumif in column B to total up values in column H from the second sheet called "Worksheet" if the name in the drop down column A matches any of the names in "Worksheets" column A.

    I want to add a criteria that the Checkbox in column D in "Worksheet" be checked (=True) to include that row in the sum.

    https://docs.google.com/spreadsheets/d/1qowl-flGEafWZJCvlf07GCrOWqsfdo68w8XA_S8Uad0/edit?usp=sharing

    1. Hello Alex,

      Try this formula:
      =SUMIFS(Worksheet!H$3:H$100,Worksheet!A$3:A$100,A2,Worksheet!D$3:D$100,TRUE)

  11. Hello,
    from AL to BO i have scores (they range from 1 to 3) and from BS to CV i have the matching values either a or b or c. I would like to match total scores for a, b, or c for each row (one individual who completes a test).
    I have tried many formulas that at best gives me a 0 or an error.
    for example:
    =SUMIFS(BS3:CV3;"a";AL3:BO3) gives a 0
    =SUMIFS(AL2:AO2,BS2:BT2,"a") gives an #ERROR

    am I trying something impossible to solve or could you provide me with some hints?
    Thanks in advance for your time.

    1. Hello Giulia,

      For me to be able to help you, please create a sample spreadsheet with 2 sheets: (1) a sample of your data (2) the result you expect to get; and share it with us – support@apps4gs.com. The result sheet is of great importance and often gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and do my best to help.

  12. Hey, how can I make this work for even or odd rows? Here’s what I’ve come up so far, but it says the argument must be a range:

    =SUMIF(ISEVEN(ROW(C62:C76)),""&"",ISEVEN(ROW(A62:A76)))

    1. Hey Dyn,

      To sum numbers from the even rows, try a formula like this:
      =SUM(FILTER(C62:C76,ISEVEN(ROW(C62:C76))))

      If this doesn't work as you expect, for me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task.

  13. This was extremely useful! Thank you so much!

    The equations with provided examples was so much clearer than other "support" I had found previously.

  14. Hi, is it possible to include importrange with sumif? I wanted to sum data from a spreadsheet, criteria is date and sum range is amount. I would like this to be entered into a different spreadsheet.

    1. Hi Peesot,

      My apologies for the late reply, I somehow missed your comment.

      Well, at the moment SUMIF does work with IMPORTRANGE but only if first you transfer the required range with a separate IMPORTRANGE and give the function permissions to access another spreadsheet. Otherwise, your SUMIF will return 0.

      You will find the tutorial on IMPORTRANGE in this blog post.

  15. Hi,
    I'm hoping you can help me with a formula.
    arrayformula with sum function a1+a2,a3+a4,a5+a6..................pair 2-2-2
    and a1+a2+a3,a4+a5+a6,a7+a8+a9....................Pair 3-3-3

  16. I'm hoping you can help! I've read through all of the queries above hoping someone would be trying to do the same thing as me and have searched other forums as well.

    Basically, I want a sum of the values in column J the value in column D is "Law" and the value in column F is "2021". The formula below returns a result of 0. What am I doing wrong?

    =SUMIF(AND('2021 EO Sales'!D:D,"Law",'2021 EO Sales'!F:F,"2021"),'2021 EO Sales'!J:J)

    1. Hello Katy,

      You're trying to sum based on two conditions using SUMIF. The SUMIF function adds up based on one condition only. The AND function won't help there.
      To sum by several conditions, you should use SUMIFS instead. It's the function described in this blog post (see above). Please look through it closely and you will be able to build the correct formula.

  17. Hi,
    I'm hoping you can help me with a formula. I have attempted to look through many forums but not quite been able to find what I'm looking for.
    I am attempting to sum multiple columns with multiple criteria that also relies on criteria from other columns and one of those has multiple criteria as well.
    Below is the outcome I am looking for
    Desired result for cell I18
    SUM all columns S,W,AA,AE,AI,AM
    IF columns
    H = AA
    K = W
    J = Jul19|Aug19|Sep19|Oct19|Nov19|Dec19|Jan20|Feb20|Mar20|Apr20|May20|Jun20
    R,V,Z,AD,AH,AL = Jul19|Aug19|Sep19|Oct19|Nov19|Dec19|Jan20|Feb20|Mar20|Apr20|May20|Jun20
    Please see below the link to the spreadsheet - this could be restricted so please let me know if you are unable to open
    https://docs.google.com/spreadsheets/d/1sOSPZ0RNObSuEdGg1YvndN_alXu77AU0QGy7J2cE0hs/edit#gid=0
    I hope to hear from you. I am desperate!

    1. Hi Nicole,

      Yes, the access to your spreadsheet is denied. For us to be able to help you, please share it with us directly: support@apps4gs.com. Please include a sheet with the result you expect to get as well.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      Thank you.

  18. Sumifs is very heard

  19. we have 3 columns as a "sum_range" .. but the results only shows 2 columns being count..

    any solution?

    thank you

      1. Thank you for sharing a spreadsheet, Pradipto.

        I can see that you refer to 3 columns with one criteria range. Please specify if you're trying to SUM the total when the name from S5 appears in at least one column (E-G) or in all three columns.

        If you need something other than that, please add the sheet with an example of what you're trying to get.

          1. Dear Pradipto,

            Thank you for editing the spreadsheet.

            I added correct formulas to the column V and highlighted them green, please take a look. I had to add up multiple SUM functions since SUMIF doesn't work the way you need. SUMIFS also won't help, since it will look for names in all 3 columns at once.

            1. Awesome !! Thank your very very much for your help.
              i will look into it a s a p..

        2. Thankyou so very2 much for responding, i'm sorry for the late reply..

          The idea is to get the total work for 1 person on certain date..
          They are working as team (of 2 or 3) .. depends on the jobs load..
          .. only 2 out of 3 columns get counted / recognized

          i'll share the latest Sheet soon..
          Thank you for your assistant .. i'm so happy for this.. :)

      1. where should we send the link to ?!
        it seems prohibited to post it here..

  20. Hi,

    I'm trying to use the SUMIF/SUMIFS OR criterion with wildcards i.e. as in your example above but

    =SUM(ARRAYFORMULA(SUMIF(A6:A14, {"*apples*", "*bananas*", "*lemons*"}, B6:B14)))

    I can only seem to get this to work with a SUMIF and not a SUMIFS - when I translate the above formula into its SUMIFS equivalent:

    =SUM(ARRAYFORMULA(SUMIFS(B6:B14, A6:A14, {"*apples*", "*bananas*", "*lemons*"})))

    the formula does not work (it only respects the first criterion "*apples*".

    Am I doing something wrong? Or is there a reason this wouldn't work with SUMIFS?

    Thanks!

    1. Hi Michael,

      As Svetlana stated above, Google Sheets SUMIFS function works with AND logic only. That is the reason why SUMIF is used for the OR logic instead.
      So I'm afraid you won't make SUMIFS work and will have to go with either SUMIF or SUMPRODUCT.

  21. =SUMIFS('p_s MastNum'!$F:$HF,'p_s MastNum'!$B:$B,$D$1,'p_s MastNum'!$C:$C,$F$1,'p_s MastNum'!$E:$E,$B$19,'p_s MastNum'!$F$3:$HF$3,$A20)

    is my formula

    I get a #Value error.

    If I remove the last criteria and use $F:$F as the range, it works. However, I want it to return/add the values in row 1 & 2. It is another filter I need to use.

    In Sheet "p_s MastNum"
    Row 1 & 2 are names
    Row 3 is a helper row joining the two names with a " "
    Col B = Month each row has a value
    Col C = Year each row has a value
    Col E = 1 of 7 headers in each row. There are more than 1 header in the column

    Please help.

    1. Hello Rain,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) source data with a formula, 2) the result you expect to get.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  22. Gracias

  23. Im trying to do a formula, not sure if the ones above are suitable - essentially I want to have a total that is increased/deducted by whether or not a number in a cell has a yes or no in the cell next to it.

    Basically I have a "bills paid out or to be paid out" section listed and then want to select yes or no next to it to say if its been paid yet or not which will then auto update the "whats left to pay out this month". so cell A1 - Car Insurance B1 - £153 C1 Yes - by changing it to yes, it doesnt calculate this in total for the month, it excludes it. but if it said no, it will include it in the total left to go out?

    Hope this makes sense!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUMIFS(B1:B10,C1:C10,"No")

      I hope this will help

  24. Hi Natalia,
    That worked perfectly!
    Thank you so much for the help. Much appreciated.

  25. Hi,
    I have check boxes in column D rows 32-160, Numbers in column E rows 32-160, and a drop down box with numbers in column I rows 32-160.
    In G19 I'm trying to enter a formula that will give me the number in each row of E multiplied by the number in corresponding row of I, if the check box in column D is checked. Then divide the resulting number by 16.
    Below is the formula that I have been playing with.
    =sumif(D32:D160, true, (E32*I32)/16:(E160*I160)/16)

    Thanks in advance,

      1. Hi Natalia,
        I tried your formula but am still having issues.
        I see what your formula does. I think I missed one thing. I want the total of my results to be in G19.

        I get the following error message:
        Array result was not expanded because it would overwrite data in G20.

        1. Hi Roger,

          The thing is that ArrayFormula processes the entire column and returns the result for each row at once. The contents of G20 (and below) doesn't allow the formula to return the outcome. Once you clear those cells, you will see the results. Try pasting the formula to an empty column – you'll see what the formula returns exactly.

          If you need the total of those, just wrap the formula in SUM:
          =SUM(ArrayFormula(IF($D$32:$D$160=TRUE,$E$32:$E$160*$I$32:$I$160,"")))

  26. i would like to add like this
    A B
    2 8
    5
    7
    6
    8
    10
    14
    15
    17
    12
    14
    i want to add top values with respect to cell reference B.if it is between 5-9 than add top 5 values in A, if B is between 11-15 than add top 10 values in A. Please help me.

    1. Hello Rajveersingh,

      Is it only one top cell in column B that you need to check? Or will other cells in column B have numbers to check as well?

  27. Thank you! you're awesome!

  28. Hi there
    I am struggling with a formula, and hope you can help me to corect it.
    SUMIFS(K2:K46;F2:F46;Q2; OM(SUM(N2:N46)>0))
    I am need the formula to add all values in column K, for the names in column F which equals to the cell Q2 ----
    But only if at the same time all the related values in row N are >0 (with related values do I mean the rows with the same name as in Q2)....
    tried it with many alternatives, but did not succed, therefore did I try to summ up the values... And now do totaly give up and beg for your support

    Thanks a lot
    Fatos

    1. I am happy with the first part of the equation (to summ/add up values in cloumn K if the text in column F = text in cell Q2
      but I really do not know how to combine, or even have a seperate equation for the second part, also same condition for text column F (=text in cell Q2) applies here for the range of rows in column N, but only if the value in those do not contain 0 aka are >0.

      Looking forward to hear a solution
      Thank you

        1. Hi Natalia,

          Thank you! In the past couple of days I came also to the same formula, but unfortunately it gives me not the result I need. So my fault, I formulated wrongly. I was not very clear I am afraid. I want the formula to calculate the total, or give me a 'yes' if both equations are ok
          also F2:F46=Q2
          AND All (the related) rows in column N are >0
          So a 'yes' if both equations are met, a 'no' if there is even a single "0" in the rows of column N (but only for the rows there first part of equation (F2:F46;Q2)

          So sorry, I am trying now for over a week, and can simply not find the solution.
          Like written before, it is even ok if I need the 'split' the formula in 2-3 parts and do first one part in a cell, then another etc.... to reach the result

          Have a great day!

          1. Hi Fatos,

            Would you mind sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: a sample of your data and the result you expect to get? I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

            Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here. I'll look into it and do my best to help.

            1. Hi Natalia,

              Thank you so much. I've shared the file "Exempel Fatos" with you
              Thanks again & kind regards

              1. Hi Fatos,

                I've looked into your file. Here's a formula you can use next to your additional column with unique names:
                =IF(MINIFS($N$2:$N$18;$F$2:$F$18;$Q2)=0;"No";"Yes")

  29. Month Job Hrs
    20-Mar 40 10
    20-Mar 60 12
    20-Mar 10 5
    20-Mar 15
    20-Apr 50 12
    21-Apr 45 8
    22-Apr 10

    20-Mar 20-Apr
    No. of Job
    No. of Hr I need a formula that will sum values from column C with the following criteria
    1. sum all hours for particular month with values in column B

    1. Hello Dante,

      Assuming the table covers 10 rows, here's the formula to sum all numbers for March:
      =ArrayFormula(SUMIFS(C2:C10,MONTH($A$2:$A$10),3,B2:B10,"<>"&""))

      You just need to adjust the ranges (to your number of rows) and change that number 3 that stands for March to a number of your month of interest.

  30. Hello! I love your content and I'm almost there with my formula :X but almost means still not.
    How to use those IF to create a formula like this.

    I have A, B, C.. columns
    I would like to make specific operation 'plus' or 'minus' bases on a cell indication.

    ANy idea?

  31. Awsomely explained (and in a "human" language!!) Excellent examples and super useful! Thanks!

  32. Help.

    It's just not working with the SUMIFS
    I've got this:
    =sumifs(uren!$E$4:$E ; uren!$A$4:$A ; C2 ; uren!$D$4:$D ; $A3 )

    uren!$E$4:$E = referring to other tab, the sum up the hours
    uren!$A$4:$A ; C2 = saying search for c2 this range on the other tab
    uren!$D$4:$D ; $A3 = saying search for c2 this range on the other tab

    What do I not see???? I checked for so many times. And the cells seem to be correct.
    It doesn't give an error, but keeps giving me a 0

    1. Hello Wouter,

      You wrote: "uren!$D$4:$D ; $A3 = saying search for c2 this range on the other tab"
      Actually, it searches for A3 in this range. Could this be the problem? Change A3 to C2 or adjust whatever lies in A3.

  33. Hi Natalia and thank you for these helpful tutorials.
    I would like to compare two text columns (on seperate sheets) lets say column A:A on sheet1 with column J:J on sheet 2. I also have a column B:B on sheet 1 with numbers. So when there is a match between Sheet1!A:A and Sheet2!J:J i would like to calculate the sum of the B:B correspondant on sheet1 . I hope it's clear ^^
    Can you help me please ?

  34. I'm having trouble finding a way to sum the exact same cells across 100+ worksheets. In this case, I'd have the worksheets all named something similar for the first part of the string (i.e "Submissions_From_Field_"Initials) while Initials will vary for each submitter. Is there a way to concatenate the worksheet name so that all of these can be included when I sum B3 across all worksheets and then won't have to type and open 100+ worksheets to get sum across all?

  35. Hello,

    I received a formula from someone else, but I need to modify it by 1 cell.

    Original formula (works perfectly, but I'm unable to modify it myself):
    =SUM(ArrayFormula(if(mod(sequence(1,COLUMNS(E6:6)),6)=1,E6:6)))

    This formula was set up to SUM every 6th column, row 6 beginning from E6, but I need to modify it to begin at F6 instead. Simply changing the formula from "E6:6" to "F6:6" returns the "#ERROR!" sign and I can't figure this out.

    I'd like to SUM F6, L6, R6, X6, AD6, etc. (Every 6th column). Plus I'll be routinely adding and deleting columns by groups of 6.

    Columns A-C are permanent and are never deleted or added to.
    Ex. Deleting columns D-I, which would delete F6
    Ex. Adding columns AB-AG which would add AD6

    This formula worked perfectly for beginning at E6, but I cannot seem to simply change it to F6 as I'm met with "#ERROR!". What am I not understanding that makes it so "E6:6" cannot simply be replaced with "F6:6"?

    Any help is much appreciated and I can't thank you enough!

    1. Hello,

      The formula works on my side if I simply replace E with F.
      You can try another formula instead:
      =SUMPRODUCT((MOD(COLUMN($A6:6)-COLUMN($A6)+1,6)=0)*($A6:6))

  36. Hi
    =SUM(E9:E13)-'Cash Paid Out'!E27 how do I add a % calc to the first bit? e.g. E24/100*19

    Many thanks

    Mike

    1. Hi Mike,

      I'm sorry I'm not getting your exact task. Could you please specify what you're trying to count? A percentage of the total? Percentage increase? If so, you may find this blog post useful.
      Otherwise, please describe the task in more detail and I'll try to help.

  37. Thank you ... this article was a life-saver and really helped bring my GSheet to life.
    Appreciate the effort to educate others.

  38. Looking for help with this:
    IF SUM(H60,H75)>86.66, 43.33-SUM(H60,H75), 0)
    • where H60 to H75 are a row of decimal values that shouldn't exceed 86.66
    • if they exceed 43.33 the amount should be reduced by the difference (86.66-SUM(H60,H75))
    For some reason this formula doesn't work. Is it syntax?

  39. And Thank You

    1. Hello Matt,

      we have a tutorial on the basics of drop-downs in Google Sheets here. You may gain some ideas for your task there.

      We could also try and help you out, but for that, I have to ask you to share your sample spreadsheet with us (support@4-bits.com) with your example data and the result you expect to get. Once you share the file, just confirm by replying here. Thank you.

  40. I have a Google sheets workbook that utilizes a cover sheet which picks up data from subsequent sheets within the book. On the cover sheet I want to add a drop-down menu that will change the data values depending on the item listen from the drop-down list.

    For example I want to see sales revenue data by country. Let's call it US, China, and Singapore. Within each sheet is a formula pulling in data for a hardcoded country, such as "US". I want to make this dynamic and changed based on the drop-down menu selection made. So if China is picked the SUMIFS will start looking for China.

    Thoughts?

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

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

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

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

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

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

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

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

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

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

  50. Amazing, 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 :)