5 Excel COUNTIF Function Issues & Fixes

Are you the one facing various issues while using the Excel COUNTIF function, then this article is for you?

In my earlier blogs, I have discussed about Excel functions like: Matrix, Transpose, Trim, TextJoin, VLookup and problem faced meanwhile using these functions.

Today in this article I’ll describe the issues related to the Excel useful function – COUNTIF.

Here check out some very frequently encountered Excel countif not working issues and ways to fix it.

About Excel Countif Function:

MS Excel is undoubtedly one of the most powerful programs of Microsoft Office suite. Due to its flexibility and ease of use, this is used worldwide to carry out different tasks.

Microsoft Excel is used by scientists, entrepreneurs, analytics, professionals, students, etc to carry out different tasks like solve complex calculations, maintain stats, maintain data, create reports, etc.

This is the reason Microsoft Excel is incorporated with various useful functions and features such as Vlookup, CountIF, Pivot Table, Fill handle and many others. But in many cases, this does not perform the same and starts showing errors.

Excel’s COUNTIF function is very useful and used to make the task easy. The COUNTIFS function counts the number of cells in a range the matches the given criteria or this returns the cells count that meets one or more criteria. This can be used with the criteria based on dates, numbers, text, and other conditions.

However apart from its uses, in many cases, users are found reporting Excel Countif Issues.

So just check out why Excel CountIF function not working and ways to fix it.

Excel COUNTIF Problems And Fixes:

Problem 1# Excel COUNTIF/COUNTIFS #Value Error

This is an issue related to the cells in a closed workbook. When the COUNTIF/COUNTIFS functions refer to the cell or range in a closed workbook result in #VALUE! Error. This is a common issue related to many other Excel functions like SUMIF, SUMIFS, COUNTBLANK, etc.

SOLUTION:

To fix the COUNTIF #Value error, open the linked workbook specifies in the formula and hit F9 to refresh the formula. Doing this will fix the #value error.

Problem 2# COUNTIF Not Working

This is a very irritating issue and there are no particular reasons behind facing the COUNTIF function not working.

So, here it is recommended to check your formula twice as a small mistake can become a big headache for you.

SOLUTION:  

• The very first thing to solve the COUNTIF not working issue is to be careful while writing the formula
• Secondly, check the symbols carefully. Microsoft have decided to separate the parameters users need to make use of ; ( semicolon) instead of using, (comma)

Please Note: If your issue is resolved, then it emerges that the ‘List Separator’ character in Windows > Control Panel > Region and Language > Additional Settings > Number> List Separator is a semicolon instead of a comma. You can change it back to the comma and your formula in Excel starts working again.

• Also, remember to add the (*asterisks). The * asterisks are the wildcards for any characters. This means “count if this cell includes the substring anywhere in the cell”. So it necessary to place it before or after the search string once to get the correct answer.

Problem 3# COUNTIF Function Too Slow

In many cases, users are found reporting that COUNTIF Function is very slow. Well, this is a very irritating situation but can be solved easily.

SOLUTION:

• The reason behind COUNTIF’s slow functioning can be your data, trying to place the number in ascending order and restrict the ranges.
• Turn off the automatic calculations like the Formulas ribbon tab, calculation options drop-down, manual
• Turn off the option “Recalculate workbook before saving” (Options, formulas tab, calculation options section)
• Change reference to column A to reference a dynamic named range.

Problem 4# Countif Function Not Counting All the Values in Range

This is another common issue that many users are found reporting while using the COUNTIF function. Users are getting the result zero in all cells.

SOLUTION:

• Well this can be a formatting issue so check what goes wrong
• Also in excel if the range contains irregular cells (empty row or column) than the range will be reduced at the empty cell the part of the empty row or column.
• The COUNTIF function not directly maintains data or ranges based on the background color or data cells font color. Remember Excel only support the definition of User-Defined Functions (UDFs) by using the Microsoft Visual Basic for Applications Editor, for supporting Excel operations on cells and tables using color for data marking.

Problem 5# COUNTIF Function Display Wrong Value Returned For Long Strings

In many cases, users are found reporting that making use of the COUNTIF function displays the wrong value for the long strings.

So here know what goes wrong:

SOLUTION:

• The COUNTIF function returns the incorrect result if you are trying to match the strings longer than 255 characters.
• So check for it and to match strings longer than 255 characters make use of the CONCATENATE functions or the concatenate operator &. For instance: = COUNTIF (A3:A6, “long string” & “another long string”).

So, these are some of the common Excel COUNTIF function issues and its fixes. Hope after following the given fixes you are able to solve Excel countif not working problem.

FAQ:

Conclusion:

So this is all about the Excel COUNTIF function issues.

I tried my best to put together the common issues that many Excel users face from time to time. I also tried my best to provide the working solutions to fix Excel countif not working issues.

Read the article to get rid of the COUNTIF issues and make use of it to carry out values easily.

Additionally, Excel is an essential application and used in daily life, so it is recommended to handle the Excel file properly and follow the best preventive steps to protect your Excel files from getting corrupted.

Despite it, always create a valid backup of your crucial Excel data and as well scan your system with a good antivirus program for virus and malware infection.

Due to any accidental or corruption cause if you end up to lose your Excel file data then use the Excel repair tool to recover it back. As this tool very efficiently repair corrupt Excel file to retrieve all your lost data back without effecting it’s originality.

If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can follow us on Facebook and Twitter.

Good Luck….