Sishir Roy
The COUNTIF function is a fantastic tool for counting any values based on a single criterion, including text and numeric values. The COUNTIF function is simple to use, but you can encounter errors when performing this task. In this article, I’ll demonstrate 4 simple solutions when the COUNTIF function is not working in Google Sheets.
4 Simple Solutions When COUNTIF Function Is Not Working in Google Sheets
We will use the dataset below to demonstrate 4 simple solutions when the COUNTIF function is not working in Google Sheets. The dataset includes a list of the names of the Cities, their Regions, and their GDP per Capita. Now, we’ll use the COUNTIF function to count the total number of cities in the North America region of our dataset.
Solution 1. Inserting Function Name Correctly
One possible cause of the COUNTIF function not functioning in Google Sheets is that you typed the function name incorrectly. Therefore, be sure you’ve checked your spelling before using the COUNTIF function. The following formula has been added to our dataset-
=COUNIF(C5:C12,"North America")
A #NAME? error has been generated because we made a typing mistake. We typed COUNIF instead of COUNTIF.
Steps:
- Firstly, select Cell D14 to apply the formula. Next, type the formula below and press Enter–
=COUNTIF(C5:C12,"North America")
- Thus, you will get the correct result.
Read More: How to Use COUNTIF Function with OR Logic in Google Sheets
Solution 2. Checking Correct Delimeter
Using the incorrect delimiter is another potential reason why Google Sheets’ COUNTIF function is not working. When entering any function in Google Sheets, the delimiter is the symbol we use to input multiple parameters. A comma (,) is typically used as a delimiter. A #ERROR! will result from using the incorrect delimiters. We have used the following formula in our dataset-
=COUNTIF(C5:C12."North America")
The result returns a #ERROR! since we utilized the dot (.) as our delimiters.
Steps:
- To apply the formula, first, choose Cell D14. Then, enter the formula below using the proper delimiter and press Enter–
=COUNTIF(C5:C12,"North America")
- Thus, you will get the desired output.
Read More: Google Sheets Count Cells from Another Workbook with COUNTIF Function
Solution 3. Applying Quotation Mark for Text
When utilizing the COUNTIF method, you might want to set the argument with a text. Any text value that is not included in double quotes will cause Google Sheets to fail to recognize the function. We have used the following formula in our dataset-
=COUNTIF(C5:C12,North America)
As the text North America is not enclosed in double quotes, it’s showing a #ERROR! as an output.
Steps:
- Firstly, pick Cell D14 to apply the formula. Next, type the formula below with the text enclosed in double quotes and hit Enter–
=COUNTIF(C5:C12,"North America")
- As a result, it will show you the correct result.
Similar Readings
- Use COUNTIF Function to Count Checkbox in Google Sheets
- How to Execute Case Sensitive COUNTIF in Google Sheets
- COUNTIF with Greater than and Less than Criteria in Google Sheets
Solution 4. Using Asterisk Wildcard Character
Assume that we want to know the total number of cities in the America region, which includes North America and South America, rather than just the total number of cities in the North America portion of our dataset. Now, the COUNTIF function will fail if we enter the term America in double quotation marks since no cell includes the word America alone. Therefore, in order for the COUNTIF method to perform correctly, we must utilize wildcard characters. We have used the following formula in our dataset-
=COUNTIF(C5:C12,"America")
It’s showing 0 as no cell contains the word America alone. So, to get the correct result, follow the below steps.
Steps:
- Firstly, select Cell D14 and apply the formula below with the wildcard character (*) before the text America. After that, press Enter–
=COUNTIF(C5:C12,"*America")
- Thus, you will get the total number of cities in the America region for the dataset.
Read More: How to Use COUNTIF for True Condition in Google Sheets
Solution 5. Selecting Correct Format
Another probable cause of the COUNTIF function in Google Sheets not functioning is choosing the incorrect format for the cells. When entering numbers, make sure your data is in Number format. Assume for the moment that we are interested in discovering the total number of cities in our dataset with a GDP per capita of more than $60,000. So, in our dataset, we have used the formula below-
=COUNTIF(D5:D12,">60000")
- Despite the fact that we employed the formula correctly, a mistake was detected. The outcome can’t be zero. But even so, we obtain the result 0. It is as a result of the incorrect format of our data.
Steps:
- Firstly, select the entire dataset that contains numbers. In our case, we selected Cell D5:D12. Next, go to the More formats icon from the top menu bar and select the Number format for the numbers.
- As a result, you will obtain the desired output.
Read More: How to Use VLOOKUP with COUNTIF Function in Google Sheets
Conclusion
In this article, I’ve shown 4 simple solutions when the COUNTIF function is not working in Google Sheets. I hope this will help you to find your mistakes while using the COUNTIF function. Please feel free to ask any queries or suggestions in the comment section below. To explore more of these helpful articles about Google Sheets, visit our website Officewheel.com.
