How to Use the COUNTIF function in Google Sheets

1. COUNTIF Function

Figure 1.1 COUNTIF Scenario

1.1 COUNT with a Condition

The COUNT and COUNTA functions are both used to count the total items within a given list. The main difference between the two is that the COUNT function only counts numerical values while the COUNTA can count any data types (e.g., texts, dates, etc.). The keywords here are total items. Both functions only count the total number of items, and that’s it. If we want to determine the number of occurrences of a specific item within a list such as the scenario in Fig. 1.1, where the task is to count the number of “Yes” in Column B, we can still use COUNT or COUNTA, but it overcomplicates the solution:

=ArrayFormula(COUNT(IF(B2:B4=”Yes”,1,””)))

As we can see we need to do more extra steps in order to solve the problem. The formula is a bit complicated and inconvenient for such a simple task. Luckily, there’s a better and simpler function that we can use for this type of problem – the COUNTIF. 

1.2 What does the COUNTIF do in Google Sheets?

The COUNTIF counts the total number of a specific item within a list. In order to do this, we provide a condition that will specify on what to include in the count. It overcomes the weaknesses of COUNT and COUNTA when it comes to conditional counting. 

1.3 COUNTIF Syntax

Below is the syntax for COUNTIF:

COUNTIF takes in two arguments/inputs that are separated by commas: range and criterion. The range is the list where we want to conduct the count while the criterion is the value checked against the range. In essence, both inputs define the condition that will specify on what to count in the list. 

Let’s take a look at a basic example to help us build an intuition on how the formula works (Fig. 1.2). 

Figure 1.2 Basic example of COUNTIF 

For this scenario, we only want to count the “Yes” in Column B using the COUNTIF formula. The range is Column B and the criterion is = Yes. The formula is written in D2. 

One thing to note is that there’s a default logical operator (e.g., =, >, <=) for the criterion, which is the equal sign (=). Therefore, If we omit the logical operator, it takes in the default logical operator, which is =. Therefore, the “Yes” in the formula is the same as “=Yes.” This criterion will be checked against each of the cells in the range. 

We can think of the two inputs of the COUNTIF as an equality (or inequality) that must be satisfied. This equality (or inequality) is the condition that the COUNTIF function must test to know what to include in the count. If we look at B2, COUNTIF will check if B2 = “Yes.” If this condition is satisfied, count B2. This is also done for B3 (B3 = “Yes”) and B4 (B4 = “Yes”). We can see the result in D2, which is only two because B2 did not satisfy the condition. Therefore, B3 and B4 are the only ones that are included in the count.  

This scenario shows the intuition behind the inputs of COUNTIF. Just think of the two as parts of an equality or inequality that defines the condition. 

  1. Breaking Down the Formula

Figure 2.1 COUNTIF Function

In this section, we’ll talk about the rules and criteria for each COUNTIF input, how to write them, how COUNTIF 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 COUNTIF formula for your problems.

2.1 COUNTIF 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 COUNTIF 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: 

=COUNTIF(B2:B4,”Yes”)

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 B2:B9 means that the data or the values are in column B from row 2 to 4. 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).

=COUNTIF({“No”;”Yes”;”Yes”},”Yes”)

The formula above checks if each of the values within the list is equal to “Yes.” If true, then the corresponding value will be included in the count. 

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 COUNTIF formula while the method in this section 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 month number from the dates in B2:B9 using the MONTH function. This will generate an array containing the month number of each of the cells in B2:B9. The resulting array is used as the range for the COUNTIF:

=ArrayFormula(COUNTIF(MONTH(B2:B9),1))

This method will not work in an ordinary COUNTIF formula. We need to use the ArrayFormula function in order for this to work correctly. We can write the ArrayFormula function and put the COUNTIF inside, or once you’re done writing the COUNTIF formula, you can just press CTRL+SHIFT+ENTER, and Google Sheets will enclosed the COUNTIF 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 COUNTIF can be 1D or 2D:

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

Figure 2.3 1D Range: one row, multiple columns

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

=COUNTIF({90,95,80,85,90},”>=90″)

The array here is the same as the range B2:F2 in Fig. 2.3.

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

Figure 2.4 1D Range: one column, multiple rows

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

=COUNTIF({90;95;80;85;90},”>=90″)

  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 3 (row) by 3 (column) array)

=COUNTIF({90,85,99;89,92,80;100,75,98},”>=90″)

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. 

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 count. A normal COUNTIF 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. 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 (“”): 

=COUNTIF(B2:B6,”>=90″)

As discussed previously, 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:

=COUNTIF(B2:B6,90)

This is also the same to: =COUNTIF(B2:B6,”=90″)

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: 

=COUNTIF(B2:B6,”>=D2″)

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

=COUNTIF(B2:B6,”>=”&D2)

If D2 is 90 (Fig. 2.6), then the criterion in the formula above is the same as >= 90. If D2 changes its value, it will also update the formula. If the logical operator is the equal sign (=), then we can just write the reference D2 in the formula: =COUNTIF(B2:B6,D2).

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.,D2)

  1. Criterion Value is Constant

If criterion value is a number:

=COUNTIF(B2:B6,”>=90″)

If criterion value is a text: (Always enclose texts with quotation marks.)

=COUNTIF(B2:B4,”Yes”)

  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 count of 100-A in Column A, but the criterion value is given in a different format, see C2. We need to extract the code from the given value in C2. The RIGHT function is used to extract the code. The extracted code is then used as the criterion value. 

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 COUNTIF (Fig. 2.8). We need to enclose COUNTIF 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 COUNTIF. 

Figure 2.8 A range of criterion values in a vertical list(i.e., D2:D3)

2.2.2.2 Dimension and Size of Criterion Value

The dimension of the criterion value can be 1D (horizontal list, Fig.2.9 or vertical list, Fig., 2.8) 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. If we look at Fig. 2.8, the range consists of 3 rows while the criterion has 2 rows.

Figure 2.9 A range of criterion values in a horizontal list (i.e., E1:F1)

2.2.2.3 Data Type of Criterion Value

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

2.3 The COUNTIF Process

2.3.1 How does the COUNTIF 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 COUNTIF processes its inputs. 

2.3.1.1COUNTIF (Basic)

The task for the first scenario is to determine the total count of Sub 1 in the Subs Column (Fig. 2.10). The COUNTIF formula is written in B12. The range is the Subs column A2:A9, the criterion is equal to A12, which is Sub 1.

Figure 2.10 Getting the total count of Sub 1

Figure 2.11 Showing the Basic Process of the COUNTIF function

Each of the cells in the Subs column will be checked against the criterion (Fig. 2.11). Together, the two form the condition that the COUNTIF will check. Column G 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, then it will be included in the count. Therefore, for the given scenario, A2, A4, A6 and A9 are the cells that are included in the count.

2.3.1.2 COUNTIF (Array Formula)

The scenarios in this section require the use of the ArrayFormula function in order for the COUNTIF to work. You can either write the COUNTIF inside the ArrayFormula or press CTRL+SHIFT+ENTER after you write the COUNTIF formula, which automatically encloses the COUNTIF 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 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 counts the number of items in January, but Column B consists of complete dates (Fig. 2.12). We need to extract the month number from the dates before using it in the condition for the COUNTIF. We can do the extraction of the month number 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 COUNTIF. 

Figure 2.12 COUNTIF with the Range as an Array

The MONTH function inside the COUNTIF 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 MONTH function to a range of cells, which in a normal usage, won’t work for the MONTH function. Therefore, we need to apply the ArrayFormula to evaluate the MONTH. The ArrayFormula function must be applied to the whole formula as seen in Fig. 2.12. In essence, it lets the functions evaluate arrays or ranges of cells that are normally not accepted as inputs in the functions, and in this case it’s the input of the MONTH function. 

The Month 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 month numbers 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 H, and this will proceed just like a normal COUNTIF. 

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

  1. More than One Criterion Values:

The scenario below has two criterion values: A12 = “Sub 1” and A13 = “Sub 2” (Fig. 2.14). So how does COUNTIF interpret these criterion inputs?

Figure 2.14 COUNTIF with more than one Criterion Values

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

In essence, the COUNTIF will treat the two criterions separately. Therefore, it will form two conditions and will check each condition. This will generate two counts, one for Sub 1 and one for Sub 2. For Sub 1, it’s shown in Fig. 2.11 while Fig. 2.15 shows the result for Sub 2. The COUNTIF in this scenario performs two counts, and returns these two results in the sheet as shown in B12 and B13 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 COUNTIF Case-Sensitive?

COUNTIF is not case-sensitive. You can perform conditions with different combinations of upper and lower case, and COUNTIF 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 COUNTIF support Wildcards?

COUNTIF can have wildcards in its criterion, and  can perform partial matches. The task in the scenario below is to count the IDs that have numeral codes starting with 100 in Column A (Fig. 2.17). Wildcards consists of asterisk (*), question mark (?) and tilde (~). These symbols 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 COUNTIF with Partial Match. 

2.4 COUNTIF 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 COUNTIF 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 COUNTIF will also match the numbers of criterion and return the same number of counts 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 COUNTIF also returns the count for each criterion values as a vertical list.  If the arrangement of the criterion values is a horizontal list, it will also return the counts as a horizontal list (Fig. 2.18). 

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

Figure 2.18 The dimension, size and arrangement of the results of COUNTIF 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 COUNTIF: a number and an error. If all of the rules of the syntax are followed correctly, it will return the count that we want. Otherwise, it will return an error. 

  1. Applications of COUNTIF

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

3.1 How to formulate a COUNTIF 

  1. First we need to identify the inputs for our COUNTIF:
  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 preprocessing steps first before applying the values?
  1. Criterion: Next, we determine what is the required condition for 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. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as inputs for the COUNTIF 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 or add some functions before we can apply the inputs to the function. 

Does the format of criterion values match the format of the range? Is it more than one criterion value?

  1. Setting up the COUNTIF Formula:

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

Figure 3.1 The COUNTIF 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 COUNTIF will match the dimension, size and arrangement of criterion values. Therefore, it would also return two counts as a vertical list. The array COUNTIF 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 therefore, formula can’t fill the cells and returns an #REF error. We need to make sure that the destination cells for the output are vacant. 

3.2 How to do COUNTIF with Numbers

In this scenario, we only want to count the values that are greater than or equal to the amount in D2, which is 50 (Fig. 3.2). The range is the No. of Students column and the criterion is  >= D2. All of the inputs are in correct format, so let’s proceed in writing the formula.  

Figure 3.2 COUNTIF with the a Number Criterion Value

Steps:

  1.  In a cell where you want to write the formula (e.g., E2), write =COUNTIF(

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

Figure 3.3 Writing the COUNTIF function

  1. Next, after the open parenthesis (, write or highlight the cells that will be used as the range input. In this case, highlight cells B2 to B5. 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.  For the criterion, write the first quotation mark, and then write the greater than or equal to operator. Enclose the logical operator with the second quotation mark: 

=COUNTIF(B2:B5,”>=”

  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 (Fig. 3.5).
  1. Complete the formula by typing close parenthesis, and then, hit ENTER. You can also hit enter without typing the close parenthesis. Google Sheets will automatically complete it for you. 

Figure 3.5 Highlighting the criterion value. 

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

=COUNTIF(B2:B5,”>=50″)

3.3 How to do COUNTIF with Texts

In this scenario, we want to count the number of courses assigned 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 COUNTIF 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: =COUNTIF(A2:A5,”Van”).

Figure 3.6 COUNTIF with a Text Criterion Value

3.4 How to do COUNTIF 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 count the date entries  that are greater than or equal to the input date in A12, which is 2/1/2021. 

Figure 3.7 COUNTIF with a Date Criterion Value

Steps: 

  1. Write the = COUNTIF( 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 B2:B9.
  1. For the criterion, write the first quotation mark, and then write the greater than or equal to operator. Enclose the logical operator with the second quotation mark: 

=COUNTIF(B2:B9,”>=”

  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 A12 or highlight A12 and write a comma after:

 =COUNTIF(B2:B9,”>=”&A12

  1. Hit ENTER. The result is in B12, Fig. 3.7.

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

=COUNTIF(B2:B9,”>=2/1/2021″)

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:

=COUNTIF(B2:B9,”>=2-1-2021″)

=COUNTIF(B2:B9,”>=February 1,2021″)

=COUNTIF(B2:B9,”>=Feb 1, 21″)

Dates are treated as numerical values. Although we see this common date format (i.e., dd/mm/YYYY) displayed in the cells, the spreadsheet does not use this in its evaluation. Instead, it operates using date serials. The dates have equivalent date serials, which are the number of days from a referenced date (i.e., 12/31/1899 = 1). If you highlight the Dates and select Automatic as the Format, you’ll see that the dates will be converted to their corresponding date serials. These are the numbers that will be evaluated by the functions. Usually, we need to convert the dates written in text format, as constants or in arrays such as the formulas above into their corresponding date serials, but there are functions like the COUNTIF function that can recognize the dates in text format. Therefore, we don’t need to do an extra step to convert the dates.

3.5 How to do COUNTIF with Blank or Empty Cells

The task in the scenario below is to determine the number of classes that don’t have enrolled students (Fig. 3.8). We’ll use column B and check which cells are blank (no students). 

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 COUNTIF with a Blank or Empty Criterion Value

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

Instead of counting the classes with no enrolled students, what if we want the opposite? If we want to count the classes that have enrolled students, we need to count the cells in Column B that contain values (Fig. 3.9).  (Note: 0 is not treated as blank. )

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 COUNTIF with Non-Blank or Non-Empty Cells

3.7 How to do COUNTIF 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 count the total number of 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 A1, A3 and A4, and we can see this result in D2. 

Figure 3.10 COUNTIF 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 COUNTIF 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 COUNTIF 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 COUNTIF 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 COUNTIF 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 COUNTIF with Multiple Criteria

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

3.8.1 COUNTIF with Multiple Criteria: OR

The task in the scenario below is to count the Subs that 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 COUNTIF 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 COUNTIFS 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 (see 3.8.2). 

Figure 3.15 COUNTIF with OR Criteria

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

Figure 3.16 COUNTIF with OR Criteria (Array Version)

3.8.2 COUNTIF with Multiple Criteria: AND

The task in the scenario below is to count the total entries of Sub 1 for dates greater than or equal to 2/1/2021 (Fig. 3.17). We have two criteria: = Sub 1 and >= 2/1/2021, but the difference from Fig. 3.15 is that the two criteria here are applied to different ranges. COUNTIF 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 COUNTIF. In the Helper Column, we use this formula:

=AND(A2=”Sub 1″,B2>=DATEVALUE(“2/1/2021”))

The AND checks if a cell in column A is Sub 1 and the corresponding date in column B is >= 2/1/2021. The DATEVALUE Function is used to convert the dates in text format to it’s date serial. The AND function returns TRUE if all of the criteria/arguments are satisfied. Now, we can use the Helper Column as the range for COUNTIF 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 COUNTIF with AND Criteria using a Helper Column

We can also use the array version of COUNTIF 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 COUNTIF 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 and false values (see column F Fig. 3.19 for visualization). The 2nd array corresponds to the 2nd condition. It checks the dates if they are >= 2/1/2021. This will also return an array of true or false values (see column G 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 H Fig. 3.19). Therefore, the result of the array multiplication conforms to the rules for the range. For the criterion value, we use 1, which means that both criteria are satisfied (Fig.3.18).  

Figure 3.19 Array Equivalent of AND function 

It’s better to use COUNTIFS 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 count. 

3.9 How to do COUNTIF with 2D Range

COUNTIF can work with a 2D range as we’ve seen in Fig. 2.5. Here are the steps:

  1. Write = COUNTIF(
  1. You can write or highlight the range of cells (A1:C3) for the range input (Fig. 3.20). 
  1. Write the comma and write the criterion, and then, hit ENTER:

=COUNTIF(A1:C3,”>=90″)

Figure 3.20 COUNTIF with 2D Range (A1:C3)

3.10 How to do COUNTIF with Inputs in Another Sheet

We can have inputs of COUNTIF 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 COUNTIF with Inputs in Another Sheet (same Workbook)

The task is to count the number of Sub 1 in the Subs Column of Range Sheet (Fig. 3.21) while the criterion value and the COUNTIF function are located in the COUNTIF Sheet (Fig. 3.22). 

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

Here are the steps:

  1. Write = COUNTIF(
  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 COUNTIF 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 COUNTIF with Range in a Different Sheet in the same Workbook

Figure 3.23 Highlighting the Cells from a different Sheet

3.10.2 COUNTIF 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. The 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 COUNTIF:
=COUNTIF(IMPORTRANGE("https://docs.google.com/spreadsheets/Example","Range Sheet!A2:A9"),A2)