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.

To recover lost Excel data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

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:

What Is Countifs Formula?

COUNTIF is one such statistical function that is used to count the cells number which meet certain criteria. Suppose, you want to count how many times a particular city is appearing in the customer list.

You can understand this, in the following way:

=COUNTIF(Where you need to search?, What you want to search?)

For example:

  • =COUNTIF(A1:A6,”California”)
  • =COUNTIF(A1:A6,A4)
How To Use Countif/Countifs Formula?

Here is the syntax to use Countif formula

=COUNTIF(range,criteria)

Arguments :

range – range to evaluate.

criteria – criteria to use on range.

Here is the syntax to use Countifs formula

COUNTIFS( range1, criteria1, [range2, criteria2, … range_n, criteria_n])

Arguments :

range1 – The first range to evaluate.

criteria1 – The criteria to use on range1.

range2 – [optional] The second range to evaluate.

criteria2 – [optional] The criteria to use on range2.

What Precautions To Be Considered While Using The COUNTIF and COUNTIFS Function?

Things you need to take care while using the COUNTIFS and COUNTIF functions.

  • Count may include false duplicates
  • Specific numbers present under other numbers doesn’t get counted
  • Unable to count the text strings more than 255 characters
  • Similar text is also get counted, apart from the specific one.
What's The Difference Between COUNTIF And COUNTIFS ?

CountIF-  it count the cells that are matching well with a certain criteria.

Example:

=COUNTIF(A3:H11,”B”)

Result: 1

Explanation:

Count total number of cells having character “B” in the cell range A3:H11

Countifs- 

Countifs function is much similar like countif function. The only difference is that you can set multiple ranges and criteria . This will count total number of times the entire criteria matches.

Example:

=COUNTIFS(B2:B7,8,E1:E10,”D”)

Result: 1

Explanation:

It covers two steps:

  1. subset the range B2:B7 in which cells are having the character “D”.
  2. After that search for the corresponding values within the column E to watch out how many of them are having the second value in the corresponding row.

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….



Summary
5 Excel COUNTIF Function Issues Along with Their Fixes
Article Name
5 Excel COUNTIF Function Issues Along with Their Fixes
Description
Know why COUNTIF function not working in Excel. Also catch detail on several COUNTIF function problems &.their fixes.
Author
Publisher Name
Repair MS Excel Blog
Publisher Logo

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.