Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
In the image below the table on the left has some blank cells in column B. They are all coloured yellow.
The table on the right is using the COUNTIF function in column F to count the states and the number of blank cells.
Cell E10 is also blank but as you can see below, the formula in cell F10 is referring to cell E10 which is blank, but it doesn’t count the blank cells in column B.
The solution is to change cell E10 to.
=””
Two quotation marks together represent a blank cell in Excel.
The formula in cell F10 will then work correctly.
Instead of changing cell E10’s value, you can use this single formula in cell F2 to generate all of the values in Column F for those values in the cells of Column E. Concatenating the empty text string (“”) to the values in cells E2:E10 inside the COUNTIF function will handle the blanks correctly.
=COUNTIF(B2:B14,””&E2:E10)
Hi Rick
Thanks for sharing another way to handle blanks with the fabulous quotation marks.
Your technique even works with numeric codes – I didn’t think it would – I was wrong.
Regards
Neale
I think it works with numeric codes because the 2nd argument to COUNTIF can be text (e.g., “>3”), so it has to be able to tolerate text values.
👍✔️