In this tutorial, you will learn how to count cells that contain numbers in Google Sheets and Microsoft Excel.
Count if cell contains number
Google Sheets and Excel have a built-in function called COUNTIF that counts the specified cells. The COUNTIF function and the wildcard asterisk (*) can be used to count the number of text-valued cells in a range.
Once you are ready, we can start by using real-life scenarios to help you understand how to count cells that contain numbers in Google Sheets and Microsoft Excel.
Anatomy of COUNTIF function
Range: A collection of cells that you want to count. Range may contain references to numbers, arrays, named ranges, or numbers themselves. Values that are blank or text are ignored.
Criteria: A text string, number, expression, or cell reference that specifies which cells will be counted.
How to Count Cells that Contain Numbers in Google Sheets
First, you need to have a clean and tidy group of data to work with.
In this example, we will be looking for the total cells that contain numbers.
To do so, we simply use the COUNTIF function to look for cells that contain numbers in the range of data and sum up the total number.
We will insert the following formula:
Once we are done, the formula will return 4. This means there are 4 cells that contain numbers.
Counting Cells that Contain Numbers in Excel
Similar to the steps used on Google Sheets, we will use the same formula:
How can marketers use this formula?
So you know how to count cells that contain numbers in your spreadsheet… now what?
Here are some marketing use cases:
1) Keyword Analysis
- Gather your keyword data in a clean and organized manner in a spreadsheet. This should include the keyword and its corresponding search volume.
- Use the COUNTIF function to count the number of keywords that have a search volume above a certain threshold.
- For example, if you want to count keywords with a search volume above 1000, you could use the formula
=COUNTIF(B2:B100, ">1000"), assuming your search volume data is in column B.
- Analyze the results to identify high-volume keywords and inform your SEO strategy.
2) Backlink Analysis
- Compile your backlink data in a spreadsheet, including the URL and its associated metrics like Domain Authority (DA).
- Use the COUNTIF function to count the number of URLs with a DA above a certain level.
- For example, if you want to count URLs with a DA above 50, you could use the formula
=COUNTIF(C2:C100, ">50"), assuming your DA data is in column C.
- Use these insights to prioritize your link building efforts and target high-authority sites.
3) Content Audits
- Create a spreadsheet with various metrics for each piece of content on your site, such as word count or number of images.
- Use the COUNTIF function to count how many pieces of content fall into certain categories based on these metrics.
- For example, to count articles with more than 2000 words, you could use the formula
=COUNTIF(D2:D100, ">2000"), assuming your word count data is in column D.
- Analyze the results to understand the composition of your content and identify areas for improvement.
In this tutorial, I covered how to count cells that contain numbers in Google Sheets and Microsoft Excel.
Example Spreadsheet: Make a copy of the example spreadsheet