Forum Discussion
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.
Thanks in Advance,
Br,
Anupam
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.
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.
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
anupambit1797 It counts values that are "larger" (alphabetically) than a space " ". A space is the first ordinary character in the sort order.
