How to Use the SUMIF function in Google Sheets

1. SUMIF Function

Figure 1.1 SUMIF Scenario

1.1 SUM with a Condition

The SUM function is a powerful function that calculates the total amount for a given range of cells, but what if we only want to calculate the total amount for a specific set of cells within a list? Let’s say we want the total amount for 2019 only (Fig. 1.1). We can use the SUM function to calculate this, but we have to reference non-contiguous cells one-by-one. For a smaller data set, this is doable, but for larger data sets, referencing each of the cells based on a given condition is tedious and inconvenient. Luckily, there’s a formula that we can use to handle such a task – the SUMIF function. 

1.2 What does the SUMIF do in Google Sheets?

The SUMIF calculates the total of cells or values from a list that satisfy a given condition. It overcomes the aforementioned weaknesses of SUM function when it comes to conditional addition. 

1.3 SUMIF Syntax

Below is the syntax for SUMIF:

SUMIF takes in three arguments/inputs that are separated by commas: range, criterion and sum_range. We can divide the inputs into two categories: the condition and the values that we want to sum. The range and criterion are the inputs that make up the condition while the sum_range defines the values that we need to sum. Let’s take a look at a basic example (Fig. 1.2)  to help us build an intuition on how the formula works. 

Figure 1.2 Basic example of SUMIF 

For this scenario, we want to add the values of C2 and C3 if their corresponding values in A2 and A3, respectively, are equal to 20. The SUMIF formula is shown at the formula bar in the figure. The range is A2:A3, the criterion is 20 and the sum_range is C2:C3. The criterion written in the formula is implied as  “=20.”

Let’s look at A2 and C2 first. We can think of the first two inputs of SUMIF, the range and criterion, as an equation (or inequality) that must be satisfied. For A2 and C2, we check if A2 is equal to 20 (A2 = 20). If this is true, then we include the value of C2 in the sum. This condition is also done for A3 and C3. If A3 is equal to 20 (A3 = 20), then we also include C3 in the sum. We see that the result SUMIF written in E5 is 5 because A3 did not meet the condition. Therefore, C3 was not included in the sum. 

This scenario shows the intuition behind the inputs of SUMIF. We can have conditions that are not directly applied to the list of values that we want to sum. In the example, the condition was applied to A2:A3 and was used indirectly to determine what to sum in C2:C3. The implication of this is that the range and the sum_range must have the same size (no. of items) and dimensions (no. of rows and columns)  (see 2.2.3.2).

Now, what if we want to apply a condition directly to C2:C3? Let’s say we want to add values in C2:C3 that are equal to 5. Here’s the formula:

=SUMIF(C2:C3,5)

We set the range to C2:C3 and the criterion to 5, but where’s the sum_range? The most interesting part of this formula is that the sum_range is an optional input, which means it’s not necessary to define the sum_range. Therefore, we can omit this in the formula. So how does the SUMIF calculate the sum using the formula above? As it turns out, optional means that the input can be left out because it has a default value, and the default value for the sum_range is the first input — the range. Therefore, if the condition is applied directly to the list of values that we want to sum, then we don’t need to define the sum_range. We can omit it in the formula and it will take its default value, which is the range. 

  1. Breaking Down the Formula

Figure 2.1 SUMIF Syntax

In this section, we’ll talk about the rules and criteria for each SUMIF input, how to write them, how SUMIF processes these inputs and how do the inputs affect the output. We’ll build an intuition to make it easier for us to visualize and create a SUMIF formula for your problems.

2.1 SUMIF Input/Arguments

2.1.1 Range 

The Range is the range of cells or values that will be checked against the criterion. Together with the criterion, they formed the condition that will be checked by the SUMIF function. Here are the important rules that you need to consider for the range:

2.1.1.1 Syntax and Location of the Range

In Google Sheets, the range can be written or coded in three different ways depending on the scenario:

  1. Reference to a cell or a range of cells: 

=SUMIF(A2:A9,2019,B2:B9)

Typically, our data set is stored in a sheet, and we want to be able to use this set of data in our formulas. In order to do that, we write the references of the cells where the data set is located. In the formula above, the reference A2:A9 means that the data or the values are in column A from row 2 to 9. The reference defines the address of the data, and the formula uses the reference to access the values within the referenced cells. 

We can write the reference in the formula or highlight the cells:

Figure 2.2 Referencing cells by highlighting them

If we perform the latter, the spreadsheet automatically writes the references for us (Fig. 2.2), so we don’t have to write the references manually. Also, it’s important to know that we can refer to the cells from any sheet. This means that the cells can be:

  • in the same sheet with the function
  • in  another sheet within the same workbook
  • Or in another sheet in another workbook (using ImportRange function)

The main advantage of referencing the cells is that the formula becomes dynamic. We can change the values within the referenced cells, and the formula will update automatically in response to the changes. 

  1. Hard-coded array of values:

We can also write directly the list of values for the range. For now, we can think of an array as a list of values that are not located in any sheet. To write them directly, we need to write the list inside the curly brackets {} (see the formula below).

=SUMIF({1,2,3,4,5},”>2″)

The formula above checks if each of the values within the list is greater than 2. If true, then the corresponding value will be included in the sum. 

There are times where you don’t want to add any values within your sheets, and this method will let you do that. You don’t have to write the values in the sheet and refer to them in the formula, especially if the values are constants and the size of the list is small. You can write the values directly like the formula above.

This method is for a smaller list of values that are constants. It’s inconvenient to use this in larger sizes of data or scenarios where you want your formula to be dynamic. 

  1. Array generated from another function or expression:

The first two methods are the basic methods in writing the range in a normal SUMIF formula while this method is for special cases. 

Sometimes we don’t want to use our raw data set directly. There are times where we want to extract some information from the raw data and use that extracted information in the formula. We can also do this with the range. The formula below extracts the last character from the values of A2:A9 using the RIGHT function. This will generate an array containing the last characters of each of the cells in A2:A9, and this array is used as the range in SUMIF. 

=ArrayFormula(SUMIF(RIGHT(A2:A9,1),9,B2:B9))

This method will not work in an ordinary SUMIF formula. We need to use the ArrayFormula function in order for this to work correctly. We can write the ArrayFormula function and put the SUMIF inside, or once you’re done writing the SUMIF formula, you can just press CTRL+SHIFT+ENTER, and Google Sheets will enclosed the SUMIF with the ArrayFormula function. 

2.2.1.2 Dimension and Size of Range

In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D). 

The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value. 

The size of the data is related to the number or rows and columns. The range can be a single cell or a range of cells.

The range of the SUMIF can be 1D or 2D:

  1. One-Dimensional (1D):
    1. Horizontal List: 
  • Cells (Fig. 2.3)

Figure 2.3 1D Range: one rows, multiple columns

  • Hard-coded Arrays: (horizontal list of values are separated by commas)

=SUMIF({“John”,”May”,”John”,”Lex”},”John”,A2:D2)

The array here is the same as the range A1:D1 in Fig. 2.3.

  1. Vertical List: 
  • Cells (Fig. 2.4)

Figure 2.4 1D Range: one column, multiple rows

  • Hard-coded Arrays: (vertical list of values are separated by semicolons)

=SUMIF({“John”;”May”;”John”;”Lex”},”John”,A2:D2)

  1. Two-Dimensional (2D):
  • Cells: (Fig. 2.5)

Figure 2.5 2D Range:Multiple Rows and Columns

  • Hard-Coded Arrays: (the array below is a 2 (row) by 3 (column) array)

=SUMIF({100,100,100;200,100,200;100,200,200},100)

2.2.1.3 Data Type of Range

The range can be any data type. This means that we can check against numbers, dates, texts, boolean (i.e., TRUE and FALSE) and etc. unless the range is also the sum_range. If the range is also the sum_range, then the range must be a number to perform the sum. Date and time are treated as numerical values and can be used in the sum. However, Text and other formats will be excluded in the sum. 

2.2.2 Criterion Input

The criterion is the most important part of the condition that specify which cells are to be included in the sum. A normal SUMIF can only have one criterion, but there are ways to incorporate multiple criteria in the formula (by using ArrayFormula). 

The criterion has two parts:

  1. Logical Operators: equal sign (=), not equal to (<>), less than (<), greater than (>), less than or equal to (<=) and greater than or equal to (>=).
  1. The Criterion Value: the value we check against.

2.2.2.1 Syntax and Location of the Criterion 

The logical operator must be written first before the criterion value, and the whole criterion must be in text format, except when the logical operator is the equal sign (=) and the criterion value is a number. To write a text in the formula, we must enclose the text with quotation marks (“”): 

=SUMIF(A2:A3,”>20″)

Just like the sum_range, the logical operator in the criterion has a default value, and that is the equal sign (=). Therefore, if the condition is to check if a value is equal to another value, then we can just omit the equal sign (=) and only write the criterion value. In this case, we don’t need to enclose the criterion value with quotation marks, except for texts:

=SUMIF(A2:A3,20)

This is also the same to: =SUMIF(A2:A3,”=20″)

Next, let’s talk about the criterion value and its syntax. The first three methods are the basic methods in writing the criterion while the last is a special case. 

  1. Criterion Value from a Cell:

If the criterion value is referenced from a cell, we must not include the reference inside the quotation marks: 

=SUMIF(A2:A9,”>E1″)

The formula will treat E1 here not as the value of cell E1 but as a text “E1.” If we want the value of E1, we must join E1 using the & operator. This operator combines values into texts:

=SUMIF(A2:A9,”>”&E1)

If E1 is 2019 (Fig. 2.6), then the criterion in the formula above is the same as >2019. If E1 changes its value, it will also update the formula. If the logical operator is the equal sign (=), then we can just write the reference E1 in the formula: =SUMIF(A2:A9,E1).

Just like for the range input, criterion value can be a cell: 

  • in the same sheet with the function
  • in  another sheet within the same workbook
  • Or in another sheet in another workbook (using ImportRange function)

Figure 2.6 Criterion where the criterion value is referenced from a cell (i.e., E1)

  1. Criterion Value is Constant

If criterion value is a number:

=SUMIF(A2:A9,2019,B2:B9)

If criterion value is a text:

=SUMIF(A2:A9,”John”,B2:B9)

  1. Criterion Value generated from another Function

The criterion value can also be a value as a result from another function. The task in the scenario below (Fig. 2.7) is to determine the total of 2019. E1 contains the criterion value, but the value in E1 is a complete date, 1/1/2019. We need to extract the year first before using it as a criterion. Therefore, we use the YEAR function to extract the year from a given date. 

Figure 2.7 Criterion where the value is from another function

  1. More than One Criterion Values

We can input more than one criterion values in SUMIF (Fig. 2.8). We still need to enclose SUMIF with the ArrayFormula function for this to work. This will be covered more in Chapter 3, but it’s important to know that we can input a range or an array  of criterion values, which also means that we can also evaluate multiple criteria in the array version of SUMIF. 

Figure 2.8 An array of criterion values (i.e., D3:D5)

2.2.2.2 Dimension and Size of Criterion Value

The dimension of the criterion value can be 1D or 2D. In terms of size, it can be a single cell or an array, and it doesn’t need to be the same size as the range and sum_range. If we look at Fig. 2.8, the range and sum_range consist of 8 rows while the criterion has 3 rows.

2.2.2.3 Data Type of Criterion Value

The criterion value can be any data type (e.g., numbers, text, dates, etc.).

2.2.3 Sum_Range Input

The sum_range is the range of cells that we want to add. It is an optional input in the SUMIF function where it’s default value is the range input. Therefore, if we omit it in the formula, it takes its default value.

2.2.3.1 Syntax and Location of the Sum_Range

Unlike the range, the sum_range is not flexible when it comes to its syntax. The sum_range must be referenced directly from the cells. It can’t be written as a constant array or an array generated from another function. 

These won’t work:

=SUMIF(A2:A9,2019,{1;2;3;4;5;6;7;8}) or

=SUMIF(A2:A9,2019,Right(A2:A9,1)) 

This is the correct way of writing its syntax: 

=SUMIF(A2:A9,2019,B2:B9)

2.2.3.2 Dimension and Size of Sum_Range

It was discussed in section 1.3 that the size and dimension of sum_range must be the same with the range. It’s not a strict rule, and both can be different in size and dimension. The formula will still work, but it may not be the result that you want. Another important thing is that the range and sum_range must have the same arrangements. Let’s look at the scenario in Fig. 2.9.

Figure 2.9 A vertical list range and a horizontal list sum_range

The range in the scenario above is A1:A3. It’s 1D and has a size of 3, while the sum_range, C1:E1, is also 1D and has the same size. The only difference is their arrangement. The range is a vertical list while the sum_range is a horizontal list. You may think that this will work, and the two will match correctly. If you look at the result of SUMIF in B5, it’s only 1. If A1 matches with C1, A2 matches with D1 and A3 matches with E1, then the sum should be 4, but it’s not. The SUMIF only treats C1 as the sum_range. Therefore, it’s the only value that was included in the sum. A2 doesn’t have a corresponding value in the sum_range and so as A3. Therefore, the arrangement is important. The range dictates what should be the arrangement. If we convert the horizontal list to a vertical list, then the formula will work correctly. 

2.2.3.3 Data Type of Sum_Range

The Sum_Range can only be numerical values because we can only perform addition on numbers. Texts or strings and other formats that are not numerical will be excluded in the addition. It’s also important to note that we can also add negative numbers in SUMIF. 

2.3 The SUMIF Process

2.3.1 How does the SUMIF work?

We already had a glimpse of how it works from section 1.3, and in this section we’ll look at more detailed examples of how the SUMIF processes its inputs. 

2.3.1.1 SUMIF (Basic)

The task for the first scenario is to calculate the total amount for 2019 (Fig. 2.10). The SUMIF formula is written in E2. The range is the Year column A2:A9, the criterion is equal to D2, which is 2019, and the sum_range is the Amount column B2:B9. 

Figure 2.10 Getting the total for the year given in D2.

Figure 2.11 Showing the Basic Process of the SUMIF function

Each of the cells in the Year column will be checked against the criterion (Fig. 2.11). Together, the two form the condition that the SUMIF will check. Column C shows if the cells satisfy the condition. It shows true if a cell in the range satisfies the condition. Otherwise, it shows false. If a cell in the range satisfies the condition, the corresponding cell in the sum_range will be included in the sum. Therefore, for the given scenario, D2, D3 and D6 are the corresponding cells that are included in the total for 2019. We can see that the cells match the position of the cells in the range that passed the condition. That’s why it’s important that the range and sum_range have the same dimension, size and arrangement. 

2.3.1.2 SUMIF (Array Formula)

The scenarios in this section require the use of the ArrayFormula function in order for the SUMIF to work. You can either write the SUMIF inside the ArrayFormula or press CTRL+SHIFT+ENTER after you write the SUMIF formula, which automatically encloses the SUMIF with the ArrayFormula. So how do we know if we need to use the ArrayFormula?

We’ve discussed in the previous sections the basic methods of writing the inputs. These methods are related to the necessary properties of the inputs like the dimensions, locations,  data types and so on. We loosely define arrays as lists of values that are not located in  the sheets. This means the lists are not physically stored  in the cells in any sheet. This definition is deliberate so that we can distinguish it from a range of cells. If we use an array or a range of cells as an input to functions that normally don’t accept arrays or ranges of cells as inputs, then we need to use the ArrayFormula function in order to evaluate the inputs, but not all inputs can work this way. Some inputs like the sum_range are strict when it comes to its properties. Even if you use the ArrayFormula function, it will not work and will only return an error. 

  1. If the Range is an Array generated from another function:

The scenario below calculates the sum for the year 2019, but column A contains complete dates (Fig. 2.12). We need to extract the year from the dates before using it in the condition for SUMIF. We can do the extraction of the year in a separate column, but most of the time, we don’t want to add anything within our sheets. If the latter is the case, then we can use the formula below to perform the SUMIF. 

The YEAR function inside the SUMIF requires one input, which is a date value. One property of its input is that its size is a single value, but for this scenario we are applying the YEAR to a range of cells, which in a normal usage, won’t work for the YEAR function. Therefore, we need to apply the ArrayFormula to evaluate the YEAR. The ArrayFormula function must be applied to the whole formula as seen in Fig. 2.12. In essence, it lets the functions evaluate arrays and ranges of cells that are normally not accepted as inputs in the functions, and in this case it’s the input of the YEAR function. 

Figure 2.12 SUMIF with the Range as an Array

The Year function will be evaluated first in the array formula, and will be applied to each of the cells in the Dates ( Fig. 2.13). The result will be an array that contains the years corresponding to the cells in the Dates column. It’s important to note that this array is not shown to us in the sheet. The values within the array will be used together with the criterion as the condition as shown in column D, and this will proceed just like the basic SUMIF. 

Figure 2.13 Showing the process of SUMIF if the range is an array

  1. More than One Criterion Values:

The scenario below has two criterion values: D2 = 2019 and D3 = 2020 (Fig. 2.14). So how does SUMIF interpret these criterion inputs?

Figure 2.14 SUMIF with more than one Criterion Values

Figure 2.15 Showing the process of SUMIF when there are more than one criterion

In essence, the SUMIF will treat the two criterions separately. Therefore, it will form two conditions and will check each condition. This will generate two totals, one for 2019 and one for 2020. For 2019, it’s shown in Fig. 2.11 while Fig. 2.15 shows the result for 2020. Therefore, SUMIF performs two sum calculations, and returns these two results in the sheet as shown in E2 and E3 in Fig. 2.14. In this case, one formula returns two results that normally requires two formulas to do. This will also work for an array of criterion values. 

2.3.2 Is SUMIF Case-Sensitive?

SUMIF is not case-sensitive. You can perform conditions with different combinations of upper and lower case, and SUMIF will be able to identify the same characters (Fig. 2.16).

Figure 2.16 Range with different upper and lower case combination from Criterion Value

2.3.4 Does SUMIF support Wildcards?

SUMIF can have wildcards in its criterion, and  can perform partial matches. The task in the scenario below is to calculate the sum of the items that have “Red” in its name in column A (Fig. 2.17). Wildcards consists of asterisk (*), question mark (?) and tilde (~). These are symbols that have functions when used in formulas. The first two are used to perform partial matches like the scenario in Fig. 2.17. The tilde is used to perform conditions that contain the asterisk (*) and question mark (?) as characters and not as wildcards. 

Figure 2.17 SUMIF with Partial Match. 

2.4 SUMIF Output

2.4.1 Dimension and Size of the Output

The dimension and size of the output is dependent on the dimension and size of the criterion value. Even the arrangement of the output values will be dependent on the arrangement of the criterion values. If the criterion consists of one criterion value, then the output of the SUMIF will also be one value as we’ve seen in the common scenarios so far. If the criterion consists of an array of criterion values, the SUMIF will also match the numbers of criterion and return the same number of totals as we’ve seen in Fig. 2.14. If we look at this example, the criterion values are arranged in a vertical list, and the SUMIF also returns the sum for each criterion values as a vertical list.  If the arrangement of the criterion values is a horizontal list, it will also return the sums as a horizontal list (Fig. 2.18). 

It doesn’t matter what the dimension, size and arrangement of the range and sum_range, the output of SUMIF is dictated by the dimension, size and arrangement of the criterion values. 

Figure 2.18 The dimension, size and arrangement of the results of SUMIF is dependent on the dimension, size and arrangement of criterion values. 

2.4.3 Data Type of the Output

There can only be two outputs for SUMIF: a number and an error. If all of the rules of the syntax are followed correctly, it will return the total that we want. Otherwise, it will return an error. 

  1. Applications of SUMIF

In this section, we’ll tackle how to formulate your own SUMIF for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the SUMIF function. We just need to follow the rules, and we’ll be able to create the SUMIF formula that you want.

3.1 How to formulate a SUMIF 

  1. First we need to identify the inputs for our SUMIF:
  1. Range: It’s better to start determining the location of the values for the range. Is the range located inside the sheet or is it an array? Is it in another sheet? Do we need to do some steps first before applying the values?
  1. Criterion: Next, we determine what is the required condition of the task. Are we testing for equality (=) or inequalities (e.g., >=, <)? How about the number of criterion values? Is it located in a cell, a constant or an array? Is it in another sheet? 
  1. Sum_Range: Now that we know the condition, we need to locate the cells that we want to sum. Is it also the range? If yes, we can omit this in the formula. Otherwise, we need to provide its reference. 
  1. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as inputs for the SUMIF function.  Most of the time, we don’t have the control on the arrangement and values of our raw data. With this step, we can carefully follow the rules for each of the inputs and see if we need to take some preprocessing steps before we can apply the inputs to the function. 

Do the range and sum_range have the same dimensions (no. of rows and columns), sizes (number of entries) and arrangements (for 1D lists such as a vertical list or horizontal list)? Are the values in sum_range numbers? 

  1. Setting up the SUMIF Formula:

Step 1 and Step 2 will give us an idea if we’ll use a basic SUMIF or an array SUMIF and if SUMIF will generate a single value or an array value. We can then know where to place the formula based on this information. If the SUMIF will return an array of values, we need to make sure that SUMIF can return the results in the vacant cells. 

Figure 3.1 The SUMIF returns an #REF error due to E2 blocking the results

If we look at Fig. 3.1, the criterion values are more than one and are in a vertical list. The SUMIF will match the dimension, size and arrangement of criterion values. Therefore, it would also return two sums as a vertical list. The array SUMIF is written in E1, and the result will start from E1 to E2 because the results will be in a vertical list. If there are values in the destination cells of the array formula like E2, which contains the text BLOCK, then the array formula can’t fill in the cells with the output. There’s a value that is obstructing it, and the formula returns an error. Therefore, we need to make sure that the destination cells for the output are vacant. 

3.2 How to do SUMIF with Numbers

In this scenario, we only want to sum the values that are equal to the amount in C2, which is $100.00 (Fig. 3.2). We look at the range, which is the Amount column. The criterion is  = C2 and the sum_range is also the Amount Column. All of the inputs are in correct format, and SUMIF will be written in D2. Let’s write the formula.  

Figure 3.2 SUMIF with the a Number Criterion Value

Steps:

  1. In D2, write =SUMIF(

As you write the formula, Google Sheets intellisense will display the formulas that match what you’re typing (Fig. 3.3). You can then select from the options displayed by  pressing the TAB key or just complete the formula by writing it manually. 

Figure 3.3 Google Sheets Intellisense Feature for writing functions (Red Annotation)

  1. Next, after the open parenthesis (, write or highlight the cells that will be used as the range input. In this case, highlight cells A2 to A9. The spreadsheet will automatically write the references for you (Fig. 3.4). 

Figure 3.4 Highlighting the cells when you’re constructing the formula will automatically write the reference of the highlighted cells. 

  1. Write a comma to separate the first input, and then, highlight C2 for the criterion value. The equal sign is omitted since the condition is an equality (Fig. 3.5). 
  1. The sum_range is also the range for this scenario. Therefore, we can omit it in the formula. Complete the formula by typing close parenthesis, and then, hit ENTER. The SUMIF will return the sum automatically (Fig. 3.2). 

Figure 3.5 Highlighting the criterion value. 

If the criterion value is a constant, then we can also write the formula in this way: 

=SUMIF(A2:A9,100)

3.3 How to do SUMIF with Texts

In this scenario, we want to add the attendance for a given name in D2 (Fig. 3.6). The steps will be the same with the steps in 3.2. In E2, write the SUMIF function and highlight the inputs. If we want the criterion value to be a constant instead of referencing it from a cell, then the formula will be: =SUMIF(A2:A7,”Faye”,B2:B7).

Figure 3.6 SUMIF with a Text Criterion Value

3.4 How to do SUMIF with Dates

What if we want to check a date condition? How do we do this and how does it work? Let’s look at the scenario in Fig. 3.7. The task here is to add the amounts with dates that are less than or equal to the input date in D2, which is 3/1/2021. 

Figure 3.7 SUMIF with a Date Criterion Value

Steps: 

  1. Write the = SUMIF( in the cell where you want to show the result. For this case, it’s E2. 
  1. Highlight the range, which is the Dates Column A2:A9.
  1. For the criterion, write the first quotation mark, and then write the less than or equal to operator. Enclose the logical operator with the second quotation mark: 

=SUMIF(A2:A9,”<=”

  1. Since the criterion value is located in a cell, we need to combine the logical operator with the criterion value using the & operator. After the last quotation mark, write the & operator and then write D2 or highlight D2 and write a comma after:

 =SUMIF(A2:A9,”<=”&D2,

  1. Highlight the sum_range, which is B2:B9 and hit ENTER. The result is in E2, Fig. 3.7.

We can also write the date as a constant in the criterion:

=SUMIF(A2:A9,”<=3/1/2021″,B2:B9)

There are other valid date formats in Google Sheets. If you want to know the other date formats, just go to Format -> Number -> More Formats -> More Date and Time Formats. Here are some examples:

=SUMIF(A2:A9,”<=3-1-2021″,B2:B9)

=SUMIF(A2:A9,”<=March 1, 2021″,B2:B9)

=SUMIF(A2:A9,”<=Mar 1, 21″,B2:B9)

3.5 How to do SUMIF with Blank or Empty Cells

The task in the scenario below is to calculate the sum of the cells in January if their corresponding cells in February are blanks (Fig. 3.8). 

To check for blank or empty cells, we need to use the quotation marks with no space in between (“”). This is called an empty string. Remember that for texts, we must enclose them with quotation marks, and if we write nothing inside the quotation marks, then it’s an empty string. This is equivalent to a blank. That’s also the reason why you mustn’t put a space inside. The space is counted as a character. See the formula in the formula bar in Fig. 3.8.

Figure 3.8 SUMIF with a Blank or Empty Criterion Value

3.6 How to do SUMIF with Non-Blank or Non-Empty Cells

Instead of calculating the total for the blank cells, what if we want the opposite? The task in the scenario below is to calculate the cells in January if their corresponding cells in February are not blanks (Fig. 3.9). 

The logical operator that we need to use for this scenario is the not equal to operator (<>). In the criterion, we only need to write the not equal to operator (<>) inside the quotation marks. As discussed before, blank or empty is equivalent to not writing anything inside the quotation marks. Therefore, if we write nothing after the operator, this means that we are using the empty string as a criterion value. 

Figure 3.9 SUMIF with Non-Blank or Non-Empty Cells

3.7 How to do SUMIF with Wildcards

Wildcards are mainly used for partial matches with texts. It’s important to emphasize here that it only works if your data are in text format. There are three types of wildcards: asterisk (*), question mark (?) and tilde (~). Each one of these functions differently:

  1. Asterisk (*)

The asterisk (*) can represent any character or a text with more than one character. Let’s look at its applications for different scenarios. 

In Fig. 3.10, the ID column contains IDs with a letter code and a number code. The task is to calculate the sum of the amounts of the IDs that have 1001 as their number code. In this case, it doesn’t matter what’s the letter code in the ID as long as the number code is 1001. This is a typical example of a partial match. We are not matching the whole text but only a part of it. 

Criterion values for this type of partial matches consist of two parts: the keywords and the trivial texts. The keywords are the words or texts that we want to match against a text value. The trivial texts are the texts that are not necessary for the match.

For the first scenario here, the pattern or format of the IDs are consistent. If this is the case, then we can select which texts will be trivial. Since the keyword is 1001, any texts before this number will be trivial texts. We can then use the asterisk(*) to represent and replace the trivial texts. 

In the formula shown in the figure, we can see the criterion value as “*1001” where the asterisk (*) represents any text before 1001. Basically, this is saying that it doesn’t matter what the texts are before 1001, as long as the last four characters are equivalent to 1001. The cells that satisfy this condition are A2, A4 and A6, and we can see this result in D2. 

Figure 3.10 SUMIF with Partial Match using Asterisk (*) that replaces texts before the keyword

If the partial match is based on the letter code, then the texts after the letter codes will be the trivial texts. Therefore, we can replace them using the asterisk (*) (Fig. 3.11).

Figure 3.11 SUMIF with Partial Match using Asterisk (*) that replaces texts after the keyword

The criterion values at Fig. 3.10 and 3.11 will only work if the format of the texts in the range are consistent. In the case where the keyword can be positioned anywhere within the text, we need to replace the texts before and after the keyword with asterisks (*). In Fig. 3.12, 1001 are located at different positions within the texts within the range.

Figure 3.12 SUMIF with Partial Match using Asterisks (*) that replaces texts before and after the keyword

  1. Question Mark (?)

Question mark (?) can only represent one character. The usage is the same as with the asterisk, but we need to make sure that the format or pattern of the texts in the range must be consistent.

If we apply this wildcard in the scenario in Fig. 3.10, the formula would be:

Figure 3.13 SUMIF with Partial Match using Question Mark (?)

The question marks (?) are used here to replace the text code in the ID. Therefore, it’s important to know first the format or pattern of texts within the range. 

We can also combine the question mark (?) and asterisk (*) in the criterion value (Fig. 3.14). If you look at the formula, the asterisk replaces the trivial texts before the keyword while the question mark represents three trivial characters before the keyword 1. 

Figure 3.14 SUMIF with Partial Match using a combination of Asterisk (*) and Question Marks (?)

  1. Tilde (~)

The wildcards work by including them inside the quotation marks. If we write asterisk (*) or question mark (?) inside the quotation marks, they will not be treated as the character themselves but as how they function as wildcards. Therefore, if the condition literally includes * or ?, we can’t write the characters normally. We need to use tilde (~) together with the wildcard characters (e.g., “~*” or “~?”) in order to perform a literal match that contains * or ?.

3.8 How to do SUMIF with Multiple Criteria

Normally, we can’t evaluate multiple criteria on SUMIF, but there are special ways to perform this. We’ll tackle this one by one in this section. 

3.8.1 SUMIF with Multiple Criteria: OR

The task in the scenario below is to calculate the sum of the amounts if the Sub in the Subs column is either Sub 1 or Sub 2 (Fig. 3.15). We have two criteria: = Sub 1 or = Sub 2. The usual way of solving this type of problem is to do a SUMIF for each criterion, and then add the two results as shown in Fig. 3.15. (Notice the use of “or” in the criteria).

This method of adding two SUMIFS is usually applied on scenarios where the criterion values are checked against one range. If there are two ranges to check, then we need to use a different approach. 

Figure 3.15 SUMIF with OR Criteria

We can also use the array version of SUMIF in combination with the SUM formula (Fig. 3.16). The criteria are written as an array (horizontal list). SUMIF will return two sums: one for Sub 1 and another for Sub 2. We can then use SUM to add the resulting array of sums from the SUMIF. This will return the total for the two Subs. We still need to use the ArrayFormula function in order for this to work. 

Figure 3.16 SUMIF with OR Criteria (Array Version)

3.8.2 SUMIF with Multiple Criteria: AND

The task in the scenario below is to calculate the sum of the amounts for January for Sub1 (Fig. 3.17). We have two criteria: = Sub 1 and = January, but the difference from Fig. 3.15 is that the two criteria here are applied to different ranges. SUMIF only accepts one range input. Therefore, we need to do some preprocessing first to conform to this rule. We can add a new column (Helper Column) where we can combine the two criteria into one condition, and then, we use this new column as the range for the SUMIF. In the Helper Column we use this formula:

=AND(A2=”Sub 1″,MONTH(B2)=1)

The AND checks if a cell in column A is Sub 1 and the corresponding cell in column B is January. The MONTH Function is used to extract the month number from the date. The AND function returns TRUE if all of the criteria/arguments are satisfied. Now, we can use the Helper Column as the range for SUMIF and use =TRUE as the criterion. We don’t need to use quotation marks for Boolean Data Types (i.e., True and False).  

Figure 3.17 SUMIF with AND Criteria using a Helper Column

We can also use the array version of SUMIF instead of using a helper column. We’ll use a similar formula, but in a different way. The AND function will always return one value no matter what’s the size or dimension of its inputs. Therefore, we’ll use a different approach (Fig. 3.18).

Figure 3.18 SUMIF with AND Criteria (Array Version)

In the range, we have two arrays multiplied together. The first array is the first condition. It’s checking the cells in A2:A9 if they are equal to Sub 1. This will return an array containing true or false values (see column E Fig. 3.19 for visualization). The 2nd array corresponds to the 2nd condition. It checks for the month if it’s January. This will also return an array of true or false values (see column F Fig. 3.19). If we multiply an array of boolean values (True and False), the TRUE and FALSE will be converted into 1 and 0, respectively. Therefore, if both criteria are satisfied, the product will be 1 (TRUE*TRUE = 1*1). If one or both are false, then the product will be 0 (False*True = 0*1, False*False = 0*0). 

This is the array equivalent of the AND function. We will not see these arrays in the spreadsheet, but this is the essence of how it works. By multiplying the two arrays, we combine them into one array that contains 1 and 0 (see column G Fig. 3.19). Therefore, this conforms to the rules for the range. For the criterion value, we use 1 since the 1 means that both criteria are satisfied (Fig.3.18).  

Figure 3.19 Array Equivalent of AND function 

It’s better to use SUMIFS function or SUMPRODUCT function for multiple criteria scenarios because they are made for these types of problems. Their inputs accommodate arrays, and therefore, we don’t need to do some extra steps like adding some functions (e.g., ArrayFormula) or performing array operations in order for them to calculate the sums. 

3.9 How to do SUMIF with 2D Range

SUMIF can work with a 2D range and a 2D sum_range. The task in Fig. 3.20 is to calculate the sum of A5:A7, which values correspond to the colors in A1:C3. Here are the steps:

  1. Write = SUMIF(
  1. You can write or highlight the range of cells (A1:C3) for the range input, and then, write a comma. 
  1. Next, write the criterion. Since it’s an equality, we can omit the equal sign (=). Then, write a comma.
  1. Last, write or highlight the references for the sum_range (A5:C7). Then, hit ENTER. 

Figure 3.20 SUMIF with 2D Range (A1:C3) and Sum_Range (A5:C7)

3.10 How to do SUMIF with Inputs in Another Sheet

We can have inputs of SUMIF from another sheet. The process of constructing the formula is still the same. We just need to reference the inputs from another sheet. 

3.10.1 SUMIF with Inputs in Another Sheet (same Workbook)

The task is to calculate the sum of the amount in the Amount Column of Range Sheet (Fig. 3.21) while the criterion value and the SUMIF function are located in the SUMIF Sheet (Fig. 3.22). 

Figure 3.21 The range is located in a different sheet (Range Sheet) 

Here are the steps:

  1. Write = SUMIF(
  1. Highlight (or write) the range:

We can’t simply write A2:A9 because the range is now from a different sheet. We need to reference the cells together with the reference sheet. The fastest way to do this is to highlight the cells. While the cursor is inside the SUMIF function, we go to the Range Sheet tab. Then, we highlight the cells A2:A9, the spreadsheet automatically writes the cell references including its sheet reference (Fig. 3.23). The sheet reference always starts with the sheet name followed by an exclamation mark (!). Then, this is followed by the cell reference. 

  1. Write a comma, and then, go back to the where the criterion value is located and highlight it. Since the criterion value is in the same sheet with the formula, we don’t need to refer to its sheet reference (Fig. 3.22). Then, press ENTER. 

Figure 3.22 SUMIF with Range in a Different Sheet in the same Workbook

Figure 3.23 Highlighting the Cells from a different Sheet

3.10.2 SUMIF with Inputs in Another Sheet (in another Workbook)

If the inputs are located from another workbook, then we need to use the IMPORTRANGE function in order to pull the data from another workbook. The IMPORTRANGE requires two inputs: the URL of the workbook and the sheet and cell references. Both inputs for the IMPORTRANGE must be in text format. Therefore, we need to enclose the URL and reference with quotation marks. IMPORTRANGE requires permission to pull the data from another workbook. Once access is granted, it will automatically pull the data. 

Below is an example on how to apply the IMPORTRANGE to SUMIF:
=SUMIF(IMPORTRANGE(“https://docs.google.com/spreadsheets/Example”,”Range!A2:A9″),100)