COUNTA and ISBLANK

[anupambit1797]

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 to H we have only 2 cells C2 and D2 having text so in Result it should be 2, I tried using COUNTA, ISBLANK , but something's not working for me.. similarly for Row 3, B3,C3,D3 and F3 have some text so in Result should be 4 and so on.

Thanks in Advance,

Br,

Anupam

[#2 · Accepted solution]

@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.

[#3]

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

[#4]

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

[#5]

Thank you so much@HansVogelaar , earlier I studied # and $, but this "> " was new for me.. sorry for my short skill on Excel, but what we call such special "> " functions in excel, is there any link say where I can find the list of all such special functions using these > ,< #,$ etc..  with their definition( like what they do; can be used to do?)

Br,

Anupam

[#6]

@anupambit1797 

> is not a function but an operator. See for example Calculation operators and precedence in Excel 

$ is used to make a cell reference absolute. See Switch between relative, absolute, and mixed references 

# is relatively new - it refers to the spill range of a dynamic array formula. See Spilled range operator