SUMIF in Google Sheets with formula examples

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:

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:

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)

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:

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)

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.

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:

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)

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:

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)

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.