Comments on: SUMIF in Google Sheets with formula examples

Google Sheets has two functions to add up numbers based on conditions: SUMIF and SUMIFS. The former evaluates just one condition while the latter can test multiple conditions at a time. In this tutorial, we will focus solely on the SUMIF function. Continue reading →

Comments page 2. Total comments: 133

  1. i want to sum value of another column based on multiple matching value in a single column

    1. Have you tried any way described in this blog post? Have you had any difficulties with any particular formula?

  3. i am tracking disbursement checks. i have a simple Sum(L:L) for a total of that column. my new issue is i now got some capital back and it throws my profit percentages way off.
    Question: the data is filtered to always show most recent checks at the top. how could i Sum the check amounts (column L) only from the lines above where it says return of capital in Column O?

    1. Hello Nathan,

      Sorry but your description is a bit confusing. Please consider sharing an editable copy of your 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.
      I'll look into it and do my best to help.

      1. figured it out. temporarily
        =SUM(INDIRECT("L1:L" & MATCH("Return of Capital",O:O,0)-1))

        i need to add this manually when there is a return or else it errors saying no return found. i think i am good for now.

  4. I am trying to total several rows of "Auto - Repair", Auto - Gas", "Auto - Car Washes" for each month. Auto - Repair may have 5 rows, Auto - Gas may have 10 rows, etc.

    I'm using the formula: =SUMIF(A3:A78,"auto - repairs",B3:M78) and it is picking up the total for the entire A3:A78 entries for the month of January. Another total is picking up just the month of January for, say the specific Auto - Car Washes, when using the same formula (clicked and dragged - B3:M78 to total all entries)

    1. Hello Toni,

      I'm sorry but the problem you're having is unclear. For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) an example of your source data with formulas (2) the result you expect to get. If you have confidential information there, just replace it with some irrelevant data but keep the format.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. Thank you.

      1. I think I may have stumbled upon the correction. I was using the formula:

        =SUMIF(A3:A78,"Auto - Mercer Auto Body",C3:C78).

        I removed and it seems to have picked up the total of all Auto - Mercer Auto Body listed. I can also add a row and it will include the new amount in the total.

        Thank you!

        1. Your comment doesn't say what you removed exactly, but I'm glad you've found the solution! Feel free to stop by our comments anytime :)

  5. Hi,
    Trying to work out the correct formula to use -
    I have a spreadsheet where I input a number of products ordered.
    If 5 or more are ordered the percentage added decreases.
    Please can you assist with what formula (if possible) I would use to do the first 5 products @ percentage 1 + additional products ordered @ percentage 2 ?

    So the product line would for example show 7 products, the first 5 I want at the higher percentage, the remaining 2 at the lower percentage and then totalled.

    I thought a secondary line would need to be added and totalled, then to add them together.

    TIA

    1. Hi Hols,

      For me to understand your task better, 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. 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, please do not email there. Once you share the file, just confirm by replying to this comment.

  6. I have been over this several time and used it in many ways it's great.
    But I have one question I cannot figure.
    I have several columns that could have a number in them (Different bank accounts) that payment could have gone out of for the purchase of apples. is there a way of grouping these columns?
    ie =SUMIF(B:B,B2,(E:E+H:H+K:K+L:L))

    1. Hello Steve,

      If you want to sum multiple ranges based on the same criteria, you should add up several SUMIF formulas, like this:
      =SUMIF(B:B,B2,E:E)+SUMIF(B:B,B2,H:H)+SUMIF(B:B,B2,K:K)+SUMIF(B:B,B2,L:L)

  7. Made it to the end, great tutorial! :)

  8. I must have not been thinking clearly - I had the results in quotes, "35" in the previous IF statement. When I change the "35" to 35, then it is a number.
    So sorry for the confusion there.

    1. Hi Charlie,

      Don't be sorry, Google Sheets functions are really confusing sometimes :) I'm happy to know you pinned down the problem :)

  9. I am trying to use the SUMIF formula to add numbers in a very similar manner to the 1st example with the bananas. However, the column with the range to be added is derived from a formula that puts the dollar amount in the cells to be added. If I remove the formulas, and place a dollar amount in the cells, it works. If the formula is in the cells to determine the values, I get all 0's for my amounts. Any ideas?

    1. Hello Charlie,

      Would you mind sharing an editable copy of your table with us (support@apps4gs.com)? I kindly ask you to shorten the table to 10-20 rows.
      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.
      I'll look into it. Thank you.

      1. Yes, I can do that later today. However, I did find a workable solution. What I had to do in the reference cell was to change the If statement result to be 1*35 to make it an actual number instead of just making the result "35". This made the result an actual number to be calculated. I couldn't even get the =Sum(A5:A13) to add them up, as they were text and not numbers. I had even tried to format as numbers with no results.

        I am sure that there other ways to accomplish the same goal.

  10. Hi, firstly thanks for the great posts. They've got me further than I was. I've got stuck however and hope you can help me out. It may be I'm using the wrong type of formula but I can't find anything that describes exactly what I want and SUMIF seems closest.

    I want to work out the total of a column (H) but only if the value of one cell (H19) is 10 or more (this cell is a COUNTIF formula). I can kind of get it working but the result only gives me the value for the first cell of (H), rather than the whole column.

    This is the formula I have been trying to use: =SUMIF(H19,">="&10,H2:H15)

    Any help is much appreciated!

      1. Hi Natalia,
        I figured that might be the case but couldn't work out what the alternative was to make it work. Your suggested forumla worked perfectly, thanks very much!

  11. How do this in Google Sheets?
    pseudo
    =IF C10 is not (empty/blank/Null)
    and D10 is not (empty/blank/Null)
    SUM E9+C10-D10

    thanks, Ohashi

    1. Hello, Ohashi - and anyone reading this post -
      I'm positive that by now you have solved this formula.
      I would be appreciative if someone could reply as to what the correct formula is for this inquiry.
      Thank you!
      ~S.

  12. I am attempting to use SUMIF in a referenced sheet and am running into an error. Would someone be able to assist me or post an example on how to do this? :)
    =SUMIF(importrange(hyperlink(C4),"Sheet1!$D$1:$D$50"),"=PCO-B",importrange(hyperlink(C4),"Sheet1!$W$1:$W$50"))

      1. Thanks Natalia for the quick response. Unfortunately, the number of times I need to do this does not warrant new tabs. I will have to explore the query method.

  13. Great post!
    Solved my issues with SUMIF + OR

  14. Hi,
    how to combine not equal to something and blank cells in Sumif function?

  15. I have a dollar value in cell D3 that the range can vary. In cell I3, I want to populate either a "0" a "50" or a "100" depending on the value in D3. If the value in D3 is 00 or less then I3 should populate a 0, if it is .01 to 599.99 then I3 should populate 50. Anything over 600 should populate a 100. What is the formula I should be using in I3?

  16. Hi,
    Hope u all are safe from Corona.
    Lets explain my problem.
    I have two column A & B. I want sum like increasing A.
    For example,
    B2 is sum of A2,
    B3 is the sum of A2:A3 &
    B4 is the sum of A2:A4. Waiting for a solution.

    Thanx in advance.

    1. Hello!
      Please use the following formula in B2
      =SUM($A$2:A2)
      After that you can copy this formula down along the column.
      I hope it’ll be helpful.

  17. Look down here and figured out how to write a formal I wanted, I was doing everything right and was utterly confused why I was not getting the sum of one of my items in my left column.. this may be a newbie mistake but some of the labels in my left column had a space in front of them which caused the formula to not catch them so it worked on "supplies" but not "supplies " hope you guys avoid this mistake ! Thanks for the great information

  18. Hello again,

    I have shared with you a spreadsheet that I am needing assistance with. You will see on the first tab (Acevedo) at the bottom is a chart next to "Unit Bonus". I need the bonus to populate according to the unit number in E1 compared to the chart. If the number in E1 is lower than it should return $0. I shared it with you, it's called PRACTICE 2020 Sales Sheet.

    THank you

    1. Hello again, Kevin,

      I'm sorry but I don't see a chart on the Acevedo sheet. There are only rows of numbers in a few columns which I'm not sure how and where to apply. I kindly ask you to double-check and edit the data so it becomes clear.
      Also, you wrote: "If the number in E1 is lower than it should return $0". Please specify what records should E1 be compared to. You will then be able to test your condition using the IF function.

  19. This is my SUMIF formular - =SUMIF(A2:A27,A35,I3:I27)
    I get a formular Parse Error. the ranges are only highlighted in colours if I put a space between the range and the Criterion. However, even with the space and the ranges highlighted in their colours I get the same error. Any ideas?

    Thanks
    Dave

    1. Hello David,

      Your formula works correctly on my end. If you're still getting issues, please make sure you're formatting your formula as your locale requires (e.g. it may need a semicolon instead of a comma, just pay attention to formula suggestions). If it still doesn't work, consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com). When sharing, make sure the option 'Notify people' is checked.

      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 try to help.

  20. I am trying to calculate a numerical figure to a cell based off of 4 different values.

    Essentially, i need
    if x = full, y= 5000 or if x = 3/4 full, y =3750

    Should be pretty easy. Just keep getting error.

  21. Hi! Thanks for all the useful bits. I'm still stuck trying to get a correct formula for SUMIF. I'm pulling data from another sheet and want to sum for a specific range of dates (one month). So far I have =SUMIFS(MYERS!$C:$C, "'MYERS!'C:C">="DATE( 2019,09,01) , 'MYERS!'C:C"<="DATE( 2019,09,30)", MYERS!Q:Q)

    BTW, MYERS! is the name of the sheet I am trying to pull from. The C column has the dates that I'm looking for and the Q column is what I'm trying to add.

    1. Hi, Sarah,

      If I understand your task correctly, this should help:
      =SUMIFS(MYERS!$C:$C,MYERS!$Q:$Q,">="&DATE(2019,9,1),MYERS!$Q:$Q,"<="&DATE(2019,9,31))

  22. >0.61<0.9
    How to use this condition Sumif formula

  23. Hi,

    I'm using Color as criterion in the formula but it doesn't work. Am I doing something wrong?
    Thanks for your help.

    Andrea

      1. I'm trying to use this but it requires a data on the cell. I am constantly updating the spread sheet so some of it are blanks yet

        1. Hello Adrian,

          The add-on has different functions that count different cells. For example, SUM, COUNT, COUNTA require cells with data, while COUNTBLANK processes empty cells. You'll see more info on the add-on help page:
          https://www.ablebits.com/docs/google-sheets-count-sum-colors/#adjust-options

          If you need any assistance with our tool, please email us to support@ablebits.com and describe your task in detail. Any screenshots of your data will also help a lot. We'll do our best to help.

    1. Hello!

      The calculation is correct. Only two dates (C5 and C6) are less than the date in B1. The sum of numbers in column B in the same rows is 700.

  25. I am trying to sum a range of cells that are in the same sheet but on a different tab. They need to meet the criteria of a specific accounting code and then add then sum the amount from another cell if they meet the criteria. The formula I am using says I have a Formula parse error. Can you tell what I am doing wrong?
    =SUMIF(‘JULY 2019’!B2:B17,"=6230-30",’JULY 2019’!A2:A17)

    1. Hello Carrie,

      your formula contains curly single quotes in sheet references. Try replacing them with straight ones:
      =SUMIF('JULY 2019'!B2:B17,"=6230-30",'JULY 2019'!A2:A17)

  26. Is there a way to summarize all the cells in the found rows after executing a filter? Seems like there should be a way to do that. Is there something like a "found set" that can be used were you need to specify a range?

    1. Jim,

      no, Google Sheets will add up all cells, filtered as well.
      I should say that the SUMIF function works like the filter itself. If you specify what conditions you use to filter your data, we'll be able to suggest the correct formula.

  27. hello, I am trying to use a sumif where I am referencing a different sheet, within the formula the sum range is B:W (a square of a range) and the text turns a color indicating that it is a correct formula but will only return 0. The range to sum does contain blanks, is this the problem? When I choose the range containing the numbers for that specific criterion it does sum the numbers. thanks

  28. Hello,

    I am attempting to use SUMIF in the following way:

    Sheet1 has 488 rows of numerical data, with header data in the first row. I am trying to create a summary on Sheet2. I want to list each Column Header from Sheet1 with the sum of the values in that Column.

    On Sheet2, I have copied over the Column Headers into Column A, and want to include their Cell Reference for my criteria.

    Here is what I have tried:
    =SUMIF('Sheet1'!$G$1:$S$488,$A10,'Sheet1'!$G$1:$S$488)
    Where $A10 is the cell containing the first Column Header I want to look up.

    Even though my SUMRANGE is the same size as RANGE, it is still only returning the sum value for the 2nd row of data on Sheet1 (directly below the header row).
    Am I on the right track or do I need to use a different function?
    Many thanks for any assistance!

    1. Hello Meg,

      Thank you for your question.

      Your task looks interesting but it's quite difficult for me to reproduce exactly what you got. Could you please share a sample spreadsheet with us (support@4-bits.com)? You can replace some important info with irrelevant data and shorten the table if you'd like.
      Once you share the table, please confirm by replying here.

      Thank you.

  29. I tried using what you wrote in the "SUMIF formulas with wildcard characters (partial match)" section and I keep getting 0.

    The formula works just fine with actual characters, but once I swap any of them with the "?" wildcard it always returns 0.

    1. My bad, I didn't know I have to use date(yyyy,mm,dd) if working with dates.

      It does seem to be the case that wildcards don't work within that formula though.

      1. Hello Yair,

        Would you mind sharing your formula with us? Perhaps, we'll notice right away if anything is incorrect there.

  30. Hi Svetlana,
    Thank you very much for writing and sharing such an excellent guide to using SUMIF in Google Sheets. It greatly improved my ability to analyze my data. After a good bit of searching, it was the only tutorial I found that provided the knowledge I needed in a manner that not only showed me how to write the formulas but enabled me to fully understand the concepts behind the formula. Of-course I stopped looking once I found yours, but I have a hard time imagining there is a better one out there.

    I really liked how you structured this tutorial. It was a perfect mix of concepts and examples. The progression from simple to more complex cases made it possible for me to understand and implement the more complex scenarios. Well done!

    I'm looking forward to your tutorial on SUMIFS!

  31. You nailed it, answered my question quickly, thanks!

  32. Can you add a note that when testing for equality, you don't need to specify a comparison operator?

  33. I'm trying to use this formula to put a value in a cell on a tab called 'overview' that calculates the sum of entries in column c of a tab called 'visit reports' that contain the word school in column b of the 'visit reports' tab. =SUMIF ('Visit reports'!B1:B300, "*school*",C1:C300). It is returning zero even when I have values in both column b and c. The sheet is at https://docs.google.com/spreadsheets/d/1PQFS5_fkhGGfR_2TcAudT9KKE98s8oq0L8zlcES3mNo/edit?usp=sharing. Can anyone help?

    1. Hi.. were you able to figure out how to solve this? Can't get access into the sheet..

      1. Hi Abear,

        If you describe your task for me and specify the formula you're trying to use, I'll do my best to help you out.

    2. Wondering if you need to put the sheet name with the sum range too.
      Looks like you managed to figure it out though.

  34. Hi!
    I would like to know if there are some optimisations about SUMIF.
    I mean, is the order of criteria important for the SUMIF? (stop the check when a criterion is false) For the very massive sheets, could be interesting.
    Thank you.

  35. This is useful...However, I am looking to create a sheet that has buyer numbers and the costs of what they bought in it. So I will sort by buyer number and then want to sum what they owe. How do I use the SUMIF when I don't know what the criteria is (if buyer #2 bought 3 items, I want to sum those three columns; then if buyer # 3 bought 6 items, I want it to sum those 6 columns) I don't know how many items they will have bought. I hope that makes sense.... thanks!

  36. Hi,

    Indeed, helpful. Thank you. But i would like you to help me to use sumifs between two or more sheets. Thanks.

  37. How to do reference from a different sheet itself?

    1. Thank you for your question, Aneesh.

      To reference a cell/range from another sheet, first enter the required sheet name wrapped in single quotes, then put an exclamation mark (!), and then enter the range you refer to, for example:
      =SUMIF('Sheet2'!B15:B26,">20")

  38. thanks a lot for these great tips!

  39. Thanks a lot! this is very useful

  40. Very thorough tutorial. Love it! I've been trying to get a simple SUMIF to work and have it reference columns on another tab but Sheets does not seem to like this. Have you been able to get it to work?

    Thanks!

    ex.

    Tab to be referenced in the same sheet is called "Joint Chequing".

    =SUMIF('Joint Chequing'!C2:C1001, A1, 'Joint Chequing'!D2:D1001)

  41. Please notice that I would like to have more of Excel Tutorial in lesson, because what I'm during now is very helpful to me

    I'm learning an lot from theses lesson that I bring up off your system. I appreciate Excel and the lesson's that are giving to me when I'm uploading theses tutorial

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