SOLVED

Iron Contributor

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

Br,

Anupam

5 Replies
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: COUNTA and ISBLANK

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.

# Re: COUNTA and ISBLANK

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 ?

Br,

Anupam

# Re: COUNTA and ISBLANK

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

# Re: COUNTA and ISBLANK

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

# Re: COUNTA and ISBLANK

> 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

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: COUNTA and ISBLANK

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.