• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to count the cells in a Row

G

GN0001

Member
I have lots of rows. I need to count the cells in each row that has text and it is not blank. I used: CountA, it counted all the cells even those cells that were blanks. I used countif(Range, "<>0"), it gave me the same number. I did Countblank,it gave me counts for the cells which were empty. I assume that those cells that are counted while there is no character has invisible characters. How can I solve this problem? or how can I clean those cells that shows no text in?

Your help is greatly appreciated.

G
 
Hi GGGGG,


Try this:
Code: 
=SUMPRODUCT((ISTEXT(A1:F1)=TRUE)*1)


..where A1:F1 is your row, Press Ctrl+Shift+Enter. Hope it helps.


Regards,

Faseeh
 
Hi, GGGGG!


I disagree with you.


COUNTA function doesn't count "blank" cells, that's the main difference with COUNT function. In fact COUNTA doesn't omit blank cells but actually empty cells.


So you'd be careful about distinguishing blank cells (cells with no data displayed, even a null char "") and empty cells (cells for which if you type on another non-used cell the formula =ISBLANK(A1) Excel will display TRUE or if you type =LEN(A1) Excel will display 0 (zero).


That's to say, if those are empty cells, COUNTA has no way to count them, contradicting what you said; and if those cells display nothing or blank but the have content (formula, spaces, non-displayed chars) or they're formatted with same color for font and background or such things, COUNTA will count them for sure.


Please check again your data, do the tests explained, and get back here with the results.

Consider uploading a sample file too, it'll help people who might be able to aid you.


Regards!
 
Faseeh,

Try this: =SUMPRODUCT((ISTEXT(A1:F1)=TRUE)*1)

didn't work, it still counts the cells. The cell doesn't have anything.


SirJB7,

The cell doesn't show anything, but it shows either 0 or it is blank. How to count the cells that have texts. I am going to try what you suggested. I will get back to you very soon.

GGG
 
Hi, GGGGG!

I stick to the file upload. Wanna see what are actually those cell contents.

Regards!
 
There is nothing in the cell and I use ISBlank and it shows False. I use Len(Cell) and it gives me zero. What is the clue?


Regards,

GGGGG
 
Hi ,


When you say there is nothing in the cell , I think what you mean is that nothing is displayed ; the cell can contain a formula like =IFERROR(some_other_function_result,"") ; the IFERROR returns the "" value if the function within returns an error value. This "" will not display anything in the cell ; however , the cell is not actually blank , which is why ISBLANK returns FALSE. LEN(cell) correctly returns 0 because the cell does not contain any alphanumeric character.


Narayan
 
Hi , GGGGG!

It's evident that the cell isn't empty. Just accept it and find out why by yourself or please upload the file as requested repeatedly. There are a couple of gurus and wise guys here but I didn't see any magician around.

Regards!
 
Thank you guys so much for the help. That is my problem since the workbook has some personal information, I can't upload the file it is illegal.


I have experienced it several times that the cell doesn't have the content (which it does have) but I can't see it and computer can see it and the formula doesn't give me the result I need.


Otherwise, I have long time ago uploaded my workbook.


Any tips? Any suggestion?


I admire your support and motivation.

GN
 
Hi ,


The simplest thing to do is to copy a small range of cells where you know data is there , but the computer does not display it , to a blank workbook , and if the same behaviour persists , upload that workbook.


Otherwise , copy the cell contents and the formulae which refer to that cell , and paste both in this forum.


Without data to work on , just conjecturing will not get us any nearer to a solution.


For what it's worth , I'll give the following example :


I entered the following data into cells E3 through L3 :

[pre]
Code: 
E3       :  A i.e. the letter A
F3       :  B
G3       :  =IF(F3>E3,"",F3)
H3       :  D
I3       :  E
J3       :  F
K3       :  =MIN(H3,I3)
L3       :  =K3+G3
Then I entered the following formulae , and the results of these are shown :

=COUNT(E3:L3)              :  1
=COUNTA(E3:L3)             :  8
=COUNTBLANK(E3:L3)         :  1
=COUNTIF(E3:L3,"="&"*")    :  6
=COUNTIF(E3:L3,"")         :  1
=COUNTIF(E3:L3,"<>0")      :  7
[/pre]
You can decide which formula you want to use.


Narayan
 
Narayan,

Let me see what I can do. I can't send any file from my work anywhere. Thank you so much.

Regards,

GN
 
I have uploaded to http://www.speedyshare.com/


This is the link: http://speedy.sh/mKVsk/My-CountBlank.xlsx


Please let me know if you can see it.


Regards,

GN
 
Hi GN ,


The cells A2:H3 contain space characters. This is the reason COUNTBLANK returns 0.


If you go beyond row 10 or so , and copy the formula down , you will see that COUNTBLANK returns 8.


Use the TRIM function to remove the space characters.


Narayan
 
GN


If in A5 you put =Len(A2) excel returns 1

so there is 1 character in A2

If in A6 you put =Code(A2) excel returns 32


So Cell A2 has 1 Character with code 32 in it


So possibly you need: =Countifs(A2:I2,"<>"&Char(32))
 
You must log in or register to reply here.
Back
Top