SUMIF in Google Sheets with formula examples

The tutorial shows how to use the SUMIF function in Google spreadsheets to conditionally sum cells. You will find formula examples for text, numbers and dates and learn how to sum with multiple criteria.

Some of the best functions in Google Sheets are those that help you summarize and categorize data. Today, we are going to have a closer look at one of such functions - SUMIF - a powerful instrument to conditionally sum cells. Before studying the syntax and formula examples, let me begin with a couple of important remarks.

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, the use of SUMIFS will be covered in the next article.

If you know how to use SUMIF in Excel desktop or Excel online, SUMIF in Google Sheets will be a piece of cake for you since both are essentially the same. But don't rush to close this page yet - you may find a few unobvious but very useful SUMIF formulas you didn't know!

SUMIF in Google Sheets - syntax and basic uses

The SUMIF function is Google Sheets is designed to sum numeric data based on one condition. Its syntax is as follows:

SUMIF(range, criterion, [sum_range])

Where:

  • Range (required) - the range of cells that should be evaluated by criterion.
  • Criterion (required) - the condition to be met.
  • Sum_range (optional) - the range in which to sum numbers. If omitted, then range is summed.

As an example, let's make a simple formula that will sum numbers in column B if column A contains an item equal to the "sample item".

For this, we define the following arguments:

  • Range - a list of items - A2:A12.
  • Criterion - a cell containing the item of interest - E1.
  • Sum_range - amounts to be summed - B2:B12.

Putting all the arguments together, we get the following formula:

=SUMIF(A2:A12,E1,B2:B12)

And it works exactly as it should: Using SUMIF in Google Sheets

Google Sheets SUMIF examples

From the above example, you may have the impression that using SUMIF formulas in Google spreadsheets is so easy that you could do it with your eyes shut. In most cases, it is really so :) But still there are some tricks and non-trivial uses that could make your formulas more effective. The below examples demonstrate a few typical use cases. To make the examples easier to follow, I invite you to open our sample SUMIF Google Sheet.

SUMIF formulas with text criteria (exact match)

To add up numbers that have a specific text in another column in the same row, your simply supply the text of interest in the criterion argument of your SUMIF formula. As usual, any text in any argument of any formula should be enclosed in "double quotes".

For example, to get a total of bananas, you use this formula:

=SUMIF(A5:A15,"bananas",B5:B15)

Or, you can put the criterion in some cell and refer to that cell:

=SUMIF(A5:A15,B1,B5:B15)

This formula is crystal clear, isn't it? Now, how do you get a total of all items except bananas? For this, use the not equal to operator:

=SUMIF(A5:A15,"<>bananas",B5:B15)

If an "exclusion item" is input in a cell, then you enclose the not equal to operator in double quotes ("<>") and concatenate the operator and cell reference by using an ampersand (&). For example:

=SUMIF (A5:A15,"<>"&B1, B5:B15)

The following screenshot demonstrates both "Sum if equal to" and "Sum if not equal to" formulas in action: Sum if equal to and Sum if not equal to formulas

Please note that SUMIF in Google Sheets searches for the specified text exactly. In this example, only Bananas amounts are summed, Green bananas and Goldfinger bananas are not included. To sum with partial match, use wildcard characters as shown in the next example.

SUMIF formulas with wildcard characters (partial match)

In situations when you want to sum cells in one column if a cell in another column contains a specific text or character as part of the cell contents, include one of the following wildcards in your criteria:

  • Question mark (?) to match any single character.
  • Asterisk (*) to match any sequence of characters.

For example, to sum the amounts of all sorts of bananas, use this formula:

=SUMIF(A2:A13,"*bananas*",B2:B13)

You can also use wildcards together with cell references. For this, enclose the wildcard character in quotation marks, and concatenate it with a cell reference:

=SUMIF(A2:A13, "*"&E1&"*", B2:B13)

Either way, our SUMIF formula adds up the amounts of all bananas: SUMIF formula with wildcard characters

To match an actual question mark or asterisk, prefix it with the tilde (~) character like "~?" or "~*".

For example, to sum numbers in column B that have an asterisk in column A in the same row, use this formula:

=SUMIF(A2:A13, "~*", B2:B13)

You can even type an asterisk in some cell, say B1, and concatenate that cell with the tilde char:

=SUMIF(A2:A13, "~"&E1, B2:B13) SUMIF formula to match an actual asterisk

Case-sensitive SUMIF in Google Sheets

By default, SUMIF in Google Sheets does not see the difference between small and capital letters. For force it to teat uppercase and lowercase characters differently, use SUMIF in combination with the FIND and ARRAYFORMULA functions:

SUMIF(ARRAYFORMULA( FIND("text", range)), 1, sum_range)

Supposing you have a list of order numbers in A2:A12 and corresponding amounts in C2:C12, where the same order number appears in several rows. You enter the target order id in some cell, say F1, and use the following formula to return the order total:

=SUMIF(ARRAYFORMULA(FIND(F1, A2:A12)),1, C2:C12) Case-sensitive SUMIF formula in Google Sheets

How this formula works

To better understand the formula's logic, let's break it down into the meaningful parts:

The trickiest part is the range argument: ARRAYFORMULA(FIND(F1, A2:A12))

You use the case-sensitive FIND function to look for the exact order id. The problem is that a regular FIND formula can only search within a single cell. To search within a range, an array formula is needed, so you nest FIND inside ARRAYFORMULA.

When the above combination finds an exact match, it returns 1 (the position of the first found character), otherwise a #VALUE error. So, the only thing left for you to do is to sum the amounts corresponding to 1's. For this, you put 1 in the criterion argument, and C2:C12 in the sum_range argument. Done!

SUMIF formulas for numbers

To sum numbers that meet a certain condition, use one of the comparison operators in your SUMIF formula. In most cases, choosing an appropriate operator is not a problem. Embedding it in the criterion properly could be a challenge.

Sum if greater than or less than

To compare the source numbers to a particular number, use one of the following logical operators:

  • greater than (>)
  • less than (<)
  • greater than or equal to (>=)
  • less than or equal to (<=)

For example, to add up numbers in B2:B12 that are greater than 200, use this formula:

=SUMIF(B2:B12, ">200", B2:B12)

Please notice the correct syntax of the criterion: a number prefixed with a comparison operator, and the whole construction enclosed in quotation marks. Sum if greater than a specified number

Or, you can type the number in some cell, and concatenate the comparison operator with a cell reference:

=SUMIF(B2:B12, ">"&E1, B2:B12) Sum if greater than a number in a certain cell

You can even input both the comparison operator and number in separate cells, and concatenate those cells:
=SUMIF(B2:B12, E1&F1) Build the SUMIF criteria by concatenating the comparison operator and number.

In a similar manner, you can use other logical operators such as:

Sum if greater than or equal to 200:

=SUMIF(B2:B12, ">=200")

Sum if less than 200:

=SUMIF(B2:B12, "<200")

Sum if less than or equal to 200:

=SUMIF(B2:B12, "<=200")

Sum if equal to

To sum numbers that equal a specific number, you can use the equality sign (=) together with the number or omit the equality sign and include only the number in the criterion argument.

For example, to add up amounts in column B whose quantity in column C is equal to 10, use any of the below formulas:

=SUMIF(C2:C12, 10, B2:B12)

or

=SUMIF(C2:C12, "=10", B2:B12)

or

=SUMIF(C2:C12, F1, B2:B12)

Where F1 is the cell with the required quantity. Sum if equal to

Sum if not equal to

To sum numbers other than the specified number, use the not equal to operator (<>).

In our example, to add up the amounts in column B that have any quantity except 10 in column C, go with one of these formulas:

=SUMIF(C2:C12, "<>10", B2:B12)

=SUMIF(C2:C12, "<>"&F1, B2:B12)

The screenshot below shows the result: Sum if not equal to

Google Sheets SUMIF formulas for dates

To conditionally sum values based on date criteria, you also use the comparison operators like shown in the above examples. The key point is that a date should be supplied in the format that Google Sheets can understand.

For instance, to sum amounts in B2:B12 for delivery dates prior to 11-Apr-2024, build the criterion in one of these ways:

=SUMIF(C2:C12, "<4/11/2024", B2:B12)

=SUMIF(C2:C12, "<"&DATE(2024,4,11), B2:B12)

=SUMIF(C2:C12, "<"&F1, B2:B12)

Where F1 is the target date: Google Sheets SUMIF formulas for dates

In case you want to conditionally sum cells based on today's date, include the TODAY() function in the criterion argument.

As an example, let's make a formula that adds up the amounts for today's deliveries:

=SUMIF(C2:C12, TODAY(), B2:B12) Conditionally sum based on today's date

Taking the example further, we can find a total of past and future deliveries:

Before today: =SUMIF(C2:C12, "<"&TODAY(), B2:B12)

After today: =SUMIF(C2:C12, ">"&TODAY(), B2:B12)

Sum based on blank or non-blank cells

In many situations, you may need to sum values in a certain column if a corresponding cell in another column is or is not empty.

For this, use one of the following criteria in your Google Sheets SUMIF formulas:

Sum if blank:

  • "=" to sum cells that are completely blank.
  • "" to sum blank cells including those that contain zero length strings.

Sum if not blank:

  • "<>" to add up cells that contain any value, including zero length strings.

For example, to sum the amounts for which the delivery date is set (a cell in column C is not empty), use this formula:

=SUMIF(C5:C15, "<>", B5:B15)

To get a total of the amounts with no delivery date (a cell in column C is empty), use this one:

=SUMIF(C5:C15, "", B5:B15) Sum if blank or not blank

Google Sheets SUMIF with multiple criteria (OR logic)

The SUMIF function in Google Sheets is designed to add up values based on just one criterion. To sum with multiple criteria, you can add two or more SUMIF functions together.

For example, to sum Apples and Oranges amounts, utilize this formula:

=SUMIF(A2:A12, "apples", B2:B12)+SUMIF(A2:A12, "oranges", B2:B12)

Or, put the item names in two separate cells, say E1 and E2, and use each of those cells as a criterion:

=SUMIF(A2:A12, E1, B2:B12)+SUMIF(A2:A12, E2, B2:B12) Google Sheets SUMIF formula with multiple criteria

Please note that this formula works like SUMIF with OR logical - it sums values if at least one of the specified criteria is met.

In this example, we add values in column B if column A equals "apples" or "oranges". In other words, SUMIF() + SUMIF() works like the following pseudo-formula (not a real one, it only demonstrates the logic!): sumif(A:A, "apples" or "oranges", B:B).

If you are looking to conditionally sum with AND logical, i.e. add up values when all of the specified criteria are met, use the Google Sheets SUMIFS function.

Google Sheets SUMIF - things to remember

Now that you know the nuts and bolts of the SUMIF function in Google Sheets, it may be a good idea to make a short summary of what you've already learned.

1. SUMIF can evaluate only one condition

The syntax of the SUMIF function allows for only one range, one criterion and one sum_range. To sum with multiple criteria, either add several SUMIF functions together (OR logic) or use SUMIFS formulas (AND logic).

2. The SUMIF function is case-insensitive

If you are looking for a case-sensitive SUMIF formula that can differentiate between uppercase and lowercase characters, use SUMIF in combination with ARRAYFORMULA and FIND as shown in this example.

3. Supply equally sized range and sum_range

In fact, the sum_range argument specifies only the upper leftmost cell of the range to sum, the remaining area is defined by the dimensions of the range argument.

To put it differently, SUMIF(A1:A10, "apples", B1:B10) and SUMIF(A1:A10, "apples", B1:B100) will both sum values in the range B1:B10 because it is the same size as range (A1:A10).

So, even if you mistakenly supply a wrong sum range, Google Sheets will still calculate your formula right, provided the top left cell of sum_range is correct.

That said, it is still recommended to provide equally sized range and sum_range to avoid mistakes and prevent inconsistency issues.

4. Mind the syntax of SUMIF criteria

For your Google Sheets SUMIF formula to work correctly, express the criteria the right way:

  • If the criterion includes text, wildcard character or logical operator followed by a number, text or date, enclose the criterion in quotation marks. For example:

    =SUMIF(A2:A10, "apples", B2:B10)

    =SUMIF(A2:A10, "*", B2:B10)

    =SUMIF(A2:A10, ">5")

    =SUMIF(A5:A10, "<>apples", B5:B10)

  • If the criterion includes a logical operator and a cell reference or another function, use the quotation marks to start a text string and ampersand (&) to concatenate and finish the string off. For example:

    =SUMIF(A2:A10, ">"&B2)

    =SUMIF(A2:A10, ">"&TODAY(), B2:B10)

5. Lock ranges with absolute cell references if needed

If you plan to copy or move your SUMIF formula at a later point, fix the ranges by using absolute cell references (with the $ sign) like in SUMIF($A$2:$A$10, "apples", $B$2:$B$10).

This is how you use the SUMIF function in Google Sheets. To have a closer look at the formulas discussed in this tutorial, you are welcome to open our sample SUMIF Google Sheet. I thank you for reading and hope to see you on our blog next week!

Spreadsheet with formula examples

Sample SUMIF Spreadsheet

Table of contents

133 comments

  1. Hi

    I have an issue where if the word approved is in column F, it should sum the value in Column D, and enter it in cell D3. I am using the following formulae:
    =SUMIF(F4:F150,"approved",D4:D150)

    However, when I type approved into a cell in column F, nothing is added to D3

    Any help would be approcyated

    Thanks
    Sam

    1. Hi Sam,

      Your formula looks correct. For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with your formula (2) the result you expect to get. 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. Do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it and try to help.

  2. Hi,

    Please could you help with the formula for the following :

    The cell I need the answer in is K5, the question is - If the value in H5 is less than 100% I need to add H5 to a cell on another tab, if it does equal 100% then K5 needs to be left blank.

    I've tried sumifs but I must be missing something.

    Thanks for any help you can give .

    1. Hi Claire,

      SUMIF merely sums values based on a certain criterion. For your task, use the IF function instead. If will let you test your value against several criteria and return different results for each.

  3. Is there a way to Sum numbers but stopping when it gets to a certain number. During football season, we are summing Point Differential but our district maxes out the Point Differential at 18. So a team that has a Point Differential of positive 24 would only be at 18. I've tried the Sum and LTE but it just gives me 0 when the number 18 is not in play.

    1. Hello James,

      If I understand you correctly and you want to sum numbers from a list but stop summing when the total reaches/exceeds 18, you can try this formula:
      =MIN(18, SUM(A1:A10))

      It will return the lesser value: either 18 or your total. So when your total is less than 18, you'll see the total. When the total = or > 18, you'll see 18.

  4. Hi I have a list of instruments with an amount of there value, and a next column states if they are to be insured, need to be taken out of the insurane, or to be added to the insurance.
    At all times I would need the amount of the total value to be insured. So, the sum of all amounts, except for the amounts that are marked as "to be taking out of insurance"

    Could someone please help me with the correct way to do this?

    Thanks
    Didier

  5. Very helpful, thank you!

  6. How would you use SUMIF, for filtered data and only wish to calculate the visible rows?

    1. Hello Lima,

      When you filter data with the filter option from the toolbar, it just hides the rows but doesn't actually remove the data. Since the data is still there, functions still process it. SUBTOTAL is the only function that can skip filtered data.

      If SUBTOTAL doesn't suit your task, you can try our Multiple VLOOKUP Matches add-on. It also works like filter but returns the filtered data as a new dataset, even as a formula that you can try & wrap in SUMIFS for further calculations.

  7. thank you, very helpful!

  8. How can I have SUMIF return blank ("") instead of 0 if the criterion is not met?

  9. These example do not work here

    But if I replace the colon , with a semicolon ; then there is no Error-Message

    =SUMIF(I2:I15;"1";J2:J15) works

    =SUMIF(I2:I15,"1",J2:J15) does NOT work

    why?

    1. Actually I found the answer in the help files
      It depends what country settings you use Germany needs semicolon as separators instead of colon

      1. Happy to know you found and solved the problem, Ralph! And thank you for sharing it with us: your spreadsheet locale is the one that forces all delimiters and date formats, so it's essential to keep it in mind.

  10. Ah .. Big thanks to you dear.. Thank you very much//

  11. Dang this was intuitive. I was able to use this and got a few things working. I ran into one oddball I think that I can't figure out.

    So basically I'm trying to first locate a name in a range. Then I want it to only add up all of the negative numbers and give me that total.

    So say that I have this

    COL A COL B COL C COL D
    2 Adam -4,000 Adam
    3 Steve -5000 Steve
    4 Adam -3760
    5 Adam 5000

    I can easily just add up anything that isn't looking for the a
    =SUMIF(A2:A5,C2,B2:B5)

    Is there a way to make it so only negatives are added up? =SUMIF(A2:A5,C2,"<0",B2:B5) <---I know this won't work but just showing an example haha.
    I tried creating one like this and it spit out a number but no where close to what it should be (EX: =SUMIF(A2:A5,C2,B2:B5)&SUMIF(A2:A5,"<0",B2:B5)

  12. Hi! I'm trying to use a range of values in a column for my criterion in the SUMIF formula, yet it's only returning the value of the first cell in that column. My formula looks like this:

    | =SUMIF(C2:C, A2:A, B2:B)

    Column C represents items I've "chosen", Column A represents the items that are present to choose, and Column B contains the value of each item respectively. Once again, when I try to use the formula, it only returns the value of the first cell in Column B. Is there a way to fix this?

    1. Hi Patrick,

      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 your formula (2) the result you expect to get. If you have confidential information there, just replace it with some irrelevant data but keep the format. You can also shorten the table to 10-20 rows. I'll look into and try to help.

      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.

  13. I was wondering the sumif if you could like only count one of the thing instead of it being repeated multiple times such as
    If I have 5 bananas in 5 different cells, could I maybe only count one but still have all the slots highlighted?

  14. please guide master

    =IFERROR(SUM(IF(P2:P5006=”Ya”;0;2);IF(Q2:Q5006=”Ya”;0;2);IF(R2:R5006=”Ya”;0;2);IF(S2:S5006=”Ya”;0;2);IF(T2:T5006=”Ya”;0;2);IF(U2:U5006=”Ya”;0;2);IF(V2:V5006=”Ya”;0;2))/7;FALSE)

    when I use this formula in excel, this formula works fine (successfully), but when I use it in a spreadsheet, with the addition of the arrayformula function it doesn't work properly, when I fiddle with it, it gives an error. please guide master. as well as an example. Thank You

    below is the formula that I use on Spreadsheet

    =arrayformula(if(row(A:A)=1;”SKOR KELAYAKAN RUMAH”;ArrayFormula(IFERROR(SUM(IF(P2=”Tidak”;2;0);IF(Q2=”Tidak”;2;0);IF(R2=”Tidak”;2;0);IF(S2=”Tidak”;2;0);IF(T2=”Tidak”;2;0);IF(U2=”Tidak”;2;0);IF(V2=”Tidak”;2;0))/7;””))))

    thank you

  15. I am trying to add a specific word into the formula but from a separate TAB of the google sheet, this is the formula that is current

    =sumif('BET HISTORY'!A:A,B8,'BET HISTORY'!G:G

    HOWEVER i have 2 sports in that sheet that i am trying to separate, which are AFL and NBA but i can not seem to add the word into the formula and make it work

      1. Yep, tried both, is it because I am trying to post the results of the formula into a separate TAB?
        It either goes to 0 or ERROR

        So in summary, what I am trying to do, is post results from 2 different sports for each specific day, atm when you enter the date, it calculates both sports combined and I am trying to separate that, but I can not figure out how to, am I able to post a photo of what I am trying to get to work?

        1. No, having the formula in a separate tab doesn't break it. Besides, your sheet references look good.

          Feel free to share an editable copy of your spreadsheet with us: support@apps4gs.com. It'll be greate if you include a sheet with the result you expect to get (it 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, please do not email there. Once you share the file, just confirm by replying to this comment.

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

          1. I have just sent a editable copy, thank you for your help !

            1. Thank you for sharing the file, Joshua.

              However, it's a spreadsheet that i can view only. I've requested editing permissions. Also, for a more precise result, please enter your formulas (without anything else in cells) where you need them exactly in the file. Thank you.

              1. Do not worry i figured it out! thank you so much for your help :) you are awesome!

              3. Perfect! That one works! entered a very similar formula in E8 under "Profit/Loss" but it is reading every day as 0.00 is there anything in the formula that i have done incorrect from what i can see it should work

              4. I have just gave you permission, and i have edited the tab to show where i want the info

              5. Thank you, Joshua.

                I've entered the formulas that should help you in the NBA Results sheet. You'll just need to create similar ones for AFL on the corresponding sheet.
                When it comes to dates, use SUMIFS instead.

                Hope this is what you need :)

  16. I have so enjoyed the instructions above. Is it possible in Google Sheets to have an automated cell that enters a certain amount every month on a certain day. I have built a spreadsheet for our church finances and every month, there needs to be a transfer between accounts. It would basically be dated that on the 15th of the month, I want a cell to automatically populate with a certain amount (500.00). I have 12 sheets, one for each month of the year.

    Thank you so much,
    Brent

  17. I have got it working with semi-colons in the parentheses, NOT using commas!

    1. Hi Driek,

      This depends on your spreadsheet locale actually (File > Spreadsheet settings). Some require commas, others – semicolons :)

  18. What if the total cells i have my data on to check is not fixed.

    1. Hello Maitry,

      Sorry, I'm afraid I don't quite understand what you mean. Could you describe it more?

  19. How can I do this but instead of the sum being a range (B3;B10) it would be an actual calculation (B3+150)?
    So IF the range, IS a DATE, THEN calculate the SUM (SUM is calculated as soon as the user enters the date)

    =SUMIF($B$5:$B$54,ISDATE(B7),(C6+$G$1))

    The closest I can get is:
    =IF($B$5:$B$54,ISDATE(B9),SUM(C8+$G$1))
    which returns TRUE instead of the SUM.

    Thanks!

  20. hi im wondering if you could help me with this formula
    A= Date, B=Amount, D= category
    =SUMIF (D:D, "",B:B)
    this formula works great for grabbing everything in D an giving me a total, but what if i only want to include specific months, to give me a monthly total of expenses.
    i tried
    =SUMIF(ARRAYFORMULA(FIND( A:A,"*Jul*")),+sumif (D:D, "",B:B))
    an it come out as 0
    i tried
    =SUMIF(A:A,"*Aug*",B:B)+SUMIF(D:D,"",B:B)
    but that just added the 3 entries i have for august plus the total of the items listed in D
    id basicly like..... A (date) + D (category) = B (total "month" expences)

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