COUNTIF in Google Sheets
Wouldn’t it be good if you could pluck out specific bits information from SEO data?
Things like, specific ranking positions, URLs that contain a specific term or page view counts over a certain number.
Well you can.
The COUNTIF function lets you quickly count the number of cells that match criterion.
What is the COUNTIF Function?
The COUNTIF function is used to count the number of times a specific data is found in a selected range of cells that meets a condition. It’s a combination of the IF and the COUNT function.
The IF part of the function determines whether the data meets a certain condition. It results in either TRUE or FALSE. Then, the COUNT part of the function counts how many times that certain value of the IF part appears within a specified data range.
The syntax for the COUNTIF function is:
=COUNTIF(range, criterion)
The range is the range of cells that are tested against the criterion.
The criterion is the condition to find and count across the data range indicated in the first argument.
When to use COUNTIF for marketing stuff?
- Getting an overview of rankings across different keywords.
- Counting URLs in different categories.
- Counting up duplicates.
How To Use The COUNTIF Function in Google Sheets
You can easily write your COUNTIF formula in a few simple steps:
- Choose a cell where you want to show your results.
- Enter the “=” sign and type COUNTIF followed by an opening bracket in this active cell.
- Select the range of cells where you want to search for your criterion.
- Type a comma to separate the arguments.
- Enter the criterion that you want to apply at the selected range.
- Finally, press ENTER to get the result.
The criterion can be any of the followings:
- Number
- Expression
- Text
Let’s look at each below:
COUNTIF Formula Examples
Number
For example, count how many keywords are ranking in exactly position 5 (hey, I know it’s a specific question to be asking about rankings).
The range is the area of capacities in column B, so B2:B9. The criterion is 5 (counting the values in the data range that are exactly equal to this specified number). Thus, the formula is:
=COUNTIF(B2:B9, 5)
Expression
Count the number of days when your website had more than 7000 page views.
When supplying an expression as the criterion, you should enclose it in double quotation marks, such as “>7000” (greater than 7000) here.
=COUNTIF(B2:B12, “>7000”)
Text
One for the ecommerce and affiliate marketers here.
Count the number of products in a specified category such as “Sports”. In this case, the criterion is a textual search term (enclosed in double quotation marks).
=COUNTIF(B2:B12, “Sports”)
Counting cells which contain specific text…
You can also count the cells that are not exactly the same as your search term but contain a specified text. The textual criterion can contain wildcards, like “*” and “?” to search for partial matches.
The question mark (?) replaces one character, while the asterisk (*) replaces any number of characters.
For example, count how many items have a name that starts with “handmade”.
Here we’re looking for the word handmade followed by an asterisk wildcard that can represent any number of additional characters:
=COUNTIF(A2:A11,"Handmade*")
The COUNTIF function returns the number of cells that start with “Handmade”.
As you can see, in screenshot 1) it doesn’t count the cell A6 where the search term is not at the beginning.
In order to look for any partial matches within the cell contents, you need to put the “*” character on both sides of the search term:
=COUNTIF(A2:A11,"*Handmade*")
The COUNTIF function works with one single criterion. In case you want to count cells by checking for multiple conditions, you can use the COUNTIFS function.
Summary
IF you liked this tutorial (see what I did there) you might also like these: