Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Aug 08, 2024
Solved

COUNTA and ISBLANK

Dear Experts,                      In attached worksheet, names COUNTA_ISBLANK, in Column "I", I need to put the number of cells from "B to H" having some data as below, so for the Row num=2, from B...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 08, 2024

    anupambit1797 

    Have the data been imported/copied from a source outside Excel? The blank-looking cells are not really blank.

     

    One workaround is to use

    =COUNTIF(Table3_1[@[Text Between Delimiters.5]:[Text Between Delimiters.11]], "> ")

     

    Another is to select the entire table except for the last column, then activate the Visual Basic Editor (Alt+F11), activate the Immediate window (Ctrl+G), type

     

    Selection.Value = Selection.Value

     

    and press Enter. The COUNTA formula will then work correctly.

anupambit1797's avatar
anupambit1797
Iron Contributor
Aug 08, 2024

Thanks HansVogelaar , yes the data is imported from a txt file into excel, using PQ, Could you please educate me on your formula:-

 

=COUNTIF(Table3_1[@[Text Between Delimiters.5]:[Text Between Delimiters.11]], "> ")

 

What does this parameter "> " does ?

 

Thanks in Advance,

Br,

Anupam

HansVogelaar's avatar
HansVogelaar
MVP
Aug 08, 2024

anupambit1797 It counts values that are "larger" (alphabetically) than a space " ". A space is the first ordinary character in the sort order.

Resources