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.
- Just double click inside the cell and you will see there is an extra space in the argument of the formula.
- 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.
- Thereafter, the COUNTBLANK function will work properly. Simple isn’t it?
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.
Steps:
- To start with, choose a cell (E10) and click BACKSPACE from the keyboard.
- After the spaces are removed the output cell (H10) will automatically show 2 blank cells.
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.
- Now, in the output cell (H13) it will show a total of 3 blank cells in the range ensuring the COUNTBLANK function working properly.
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.
