If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting non blank cells
I want to count all the cells in a range that have text in them - I do not want to include any blank cells OR where a formula has returned an empty string. Counta will still count these cells? Any ideas.
-- Jack Evans |
#2
|
|||
|
|||
Counting non blank cells
=SUMPRODUCT(--(A1:A100""))
-- No private emails please, for everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "jack evans" wrote in message ... I want to count all the cells in a range that have text in them - I do not want to include any blank cells OR where a formula has returned an empty string. Counta will still count these cells? Any ideas. -- Jack Evans |
#3
|
|||
|
|||
Counting non blank cells
"Peo Sjoblom" wrote...
=SUMPRODUCT(--(A1:A100"")) Or =COUNTIF(A1:A100,"*") -- To top-post is human, to bottom-post and snip is sublime. |
#4
|
|||
|
|||
Counting non blank cells
=counta(A1:A100)
-----Original Message----- "Peo Sjoblom" wrote... =SUMPRODUCT(--(A1:A100"")) Or =COUNTIF(A1:A100,"*") -- To top-post is human, to bottom-post and snip is sublime. . |
#5
|
|||
|
|||
Counting non blank cells
"alk" wrote...
=counta(A1:A100) .... Reread the OP: "I do not want to include any blank cells OR where a formula has returned an empty string. Counta will still count these cells?" COUNTA *does* include cells evaluating to "" in its result, so fails to satisfy the OP's requirements. |
#6
|
|||
|
|||
Counting non blank cells
You can also do this with 2 for... next statements:
Sub BB Dim Col as integer Dim Row as integer Dim CountingValue as integer For Col = 1 to 100 Row = 1 For Row = 1 to 100 If trim$(len(cells(row,col).value))0 then CountingValue = CountingValue +1 End if Next Row Next Col end sub --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|