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 rather than the right click delete) the count functions then count correctly, am I missing something really basic here or is this some sort of glitch

excel
Formulas and Functions

3 Replies

  • 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