Google Sheets is a powerful tool for analyzing and organizing data. One of the most useful and practical functions it offers is the COUNTIF function. In this article, we will explain everything you need to know about this function, including why it’s important, provide numerous easy-to-follow examples, and show you how to use it in different situations. We will also demonstrate how you can use this function to count the occurrences of a specific keyword in a range of cells.
What is the COUNTIF Function?
The COUNTIF function in Google Sheets counts the number of cells in a given range that meet a specified criterion. This function is handy when you want to know how many times a specific criterion is met in a range of cells.
By default, the COUNTIF function looks for an exact match. You can also use COUNTIF formulas to evaluate whether values meet more complex conditions. For example, you can test if cell values are greater than, less than, or not equal to a certain number. You can also count all occurrences of a string or keyword within a range of cells.
Syntax of the COUNTIF Function
The COUNTIF function in Google Sheets has the following syntax:
=COUNTIF(range, criterion)rangerefers to the range of cells containing the data you want to count.criterionis the condition that must be met for a cell to be counted.
The range of cells can contain text strings or numbers. The criteria can use comparison or logical operators if the range of cells contains numbers, such as:
- Greater than (
>) - Less than (
<) - Greater than or equal to (
>=) - Equal to (
=) - Less than or equal to (
<=) - Not equal to (
<>)
For example, to count the number of sales representatives who closed more than 50 transactions in the first quarter of the year, you can use >50 as the criterion. Remember to enclose the operators in double quotation marks.
The criterion can also be a text string enclosed in double quotation marks or a cell reference containing the text string. You can include wildcard characters as part of the text string. For example, to count cells containing the text string “Open”, use "Open*" as the criterion.
Important Considerations for the COUNTIF Function
Here are some important factors to consider when using the COUNTIF function to build quality formulas and avoid common errors:
- Numeric criterion: Numeric values with exact matches do not require quotation marks.
- Conditional tests: Use logical operators to perform conditional tests and surround them with double quotation marks in your formula.
- Case-insensitive: The COUNTIF function does not differentiate between uppercase and lowercase, so you will get the same result whether you use uppercase or lowercase in your formula.
- Counting empty and non-empty cells: Use empty quotation marks (
"") to count empty cells in a range. Use the<>operator to count non-empty cells in a range. - Reference another cell: You can store the criterion for the COUNTIF function in a separate cell, which the formula can reference.
- Using wildcard characters: The COUNTIF function in Google Sheets supports three wildcard characters: asterisk (
*), question mark (?), and tilde (~).
How to Use the COUNTIF Function in Google Sheets
Now let’s set up a sample dataset in Google Sheets to demonstrate some step-by-step examples of COUNTIF.
We will use Crawlan’s Coefficient to import sales data from Salesforce into Sheets. Coefficient automatically syncs your spreadsheet with your enterprise systems and provides pre-built dashboards for popular use cases.
We will use the imported dataset below for our examples:
Now that we have our dataset, let’s dive into some step-by-step examples of using the COUNTIF function in Google Sheets.
Using COUNTIF in Google Sheets to Count Cells Containing Specific Text
As mentioned earlier, you can use a COUNTIF formula to count the number of cells containing specific text or numeric values within a data range.
For example, suppose you want to know the total number of articles sold by your sales team on your website in our dataset. To do this, you need to count the number of cells in column C containing “Website”.
Use the COUNTIF formula to count the articles in column C:
=COUNTIF(C3:C18,"Website")Using COUNTIF to Count Empty and Non-Empty Cells
You can also count the number of empty and non-empty cells within a data range using the COUNTIF function.
For example, if you want to count the number of empty cells in column A, use this formula:
=COUNTIF(A3:A18,"")Use the formula below to count the number of non-empty cells in column A:
=COUNTIF(A3:A18,"<>")If you want to count the number of non-empty cells containing text values only, use this formula:
=COUNTIF(A3:A18,"*")Conclusion
The COUNTIF function in Google Sheets is a reliable and practical tool that allows you to quickly count the occurrences of an item in a specified range of cells. The simple yet powerful capabilities of this function make your work in Google Sheets easier. You can also automate your dashboards and workflows in Google Sheets with Coefficient.
Try Coefficient for free to optimize your spreadsheets and unlock new possibilities with your data in Google Sheets. For more information and resources on Google Sheets tools and digital marketing, visit Crawlan.com.
