Forum Discussion

Lee Buzzard's avatar
Lee Buzzard
Copper Contributor
Feb 10, 2017

error counting text populated cells

I am using the countif and countA functions to count ‘text populated’ cells and noted that the count is actually counting blank cells as well, when I delete the blank cells (using the keyboard delete...
excel
Formulas and Functions
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Feb 10, 2017

Dear Lee,

 

These cells are actually not blank, it's contain zero length string like this: ""
So it's counted by COUNTA, but the cells containing zero length string look like blank.
 
Just follow this link to learn more about this issue:
http://bit.ly/2l1pRtw
  • Lee Buzzard's avatar
    Lee Buzzard
    Copper Contributor
    Feb 15, 2017

    Hi Haytham,

     

    Thanks for this, while I did not know about the zero length string, I feared something like this; at least now that I know what it is I can think of a fix.

     

    Thanks again

     

    Lee

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Feb 15, 2017

      Hi Lee, 

       

      Maybe you can deduct COUNTIFS(A1:A10,"<>"&"")

       

      or expand the COUNTIFS to = COUNTIFS(A1:A10,">0",A1:A10, "<>"&"")

       

      Not quite sure on what your exact scenario is...

Resources