[Solved]: COUNTBLANK Not Working in Excel

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we will see the COUNTBLANK function not providing the exact output we are looking for. This happens when there is a formula or unseen spaces inside the cells. For this reason, the COUNTBLANK function doesn’t count them as blank cells. Today in this article, I am sharing with you how to solve the COUNTBLANK function not working in Excel. Stay tuned!


3 Quick Methods to Solve If COUNTBLANK Function Is Not Working in Excel

In the following, I have explained 3 simple and easy methods to solve COUNTBLANK not working in Excel.


1. Rectify Formula from Cells

If you want you can rectify or change or delete the formula to make the cells blank so that the COUNTBLANK function can work properly.

In the below image, you will see we have applied the COUNTBLANK function in cells (H6:H13). But in cell (H7) the COUNTBLANK function is displaying 1 blank cell. But we can see 2 blank cells in the row. Let’s find the reason behind it and solve it.

Steps:

  • First, choose a cell (D7) and you will see we have a formula inside the cell.

Rectify Formula from Cells to solve countblank not working in Excel

  • Just double click inside the cell and you will see there is an extra space in the argument of the formula.

Rectify Formula from Cells to solve countblank not working in Excel

  • If we remove the extra space from the formula we will see the proper output and the COUNTBLANK function working smoothly.

  • You can also delete the formula from the cell (D5) by pressing DELETE.

Rectify Formula from Cells to solve countblank not working in Excel

  • Thereafter, the COUNTBLANK function will work properly. Simple isn’t it?

Rectify Formula from Cells to solve countblank not working in Excel

Read More: [Solved] Excel Formatting Not Working Unless Double Click Cell


2. Delete Extra Spaces

When you have extra spaces inside a cell it won’t count as a blank cell. You can remove the space manually to make the cell blank.

In the below screenshot, you will see we have two blank cells in a row but the output is showing only 1 blank cell.

Here, in cell (E10) we have multiple bank cells. Thus this happened. Let’s solve it now.

Delete Extra Spaces to solve Countblank not working in Excel

Steps:

  • To start with, choose a cell (E10) and click BACKSPACE from the keyboard.

Delete Extra Spaces to solve Countblank not working in Excel

  • After the spaces are removed the output cell (H10) will automatically show 2 blank cells.

Delete Extra Spaces to solve Countblank not working in Excel

Note: You can also use the TRIM function, in that case, you may need to use a new column to restore the data.

Read More: [Fixed] Excel COUNT Function Not Working


3. Remove Data from Cells

If you want you can also check by deleting data from cells. Follow the instructions below-

Steps:

  • Presently, select cells (E13:F13) and press the DELETE key.

Remove Data from Cells to solve countblank not working in Excel

  • Now, in the output cell (H13) it will show a total of 3 blank cells in the range ensuring the COUNTBLANK function working properly.

Remove Data from Cells to solve countblank not working in Excel

Read More: [Fixed!] Merge Cells Button Is Greyed Out in Excel


Count Blank Cells with Condition

Well, you can also add multiple conditions with the COUNTBLANK function using the IF function. The IF function will return an output according to the given condition.

Steps:

  • First, choose a cell (I6) and write the formula down-
=IF(COUNTBLANK(C6:G6)>0,COUNTBLANK(C6:G6),"Nil")

Where,

  • The COUNTBLANK function will count blank cells in the range (C6:G6) and return output according to it.
  • The IF function will provide an output if the blank cell is greater than “0” it will show the numeric value and if it’s not then it will display “Nil”.

  • Click ENTER and drag down “Fill Handle”.
  • Finally, we will successfully get the output where we counted cells with multiple conditions.

Read More: [Fixed!] Formulas Are Not Calculating Automatically in Excel


Things to Remember

Sometimes while transferring or gathering files from other sources your data may be contaminated by some unprintable characters, which may also cause problems while utilizing COUNTBLANK. In that case, you can use the CLEAN function to clean your data before using COUNTBLANK.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the methods to solve COUNTBLANK not working in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Wasim Akram
Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo