Results 1 to 10 of 10
Thread: COUNTIF <> '' (2002 SP3)

20060311, 21:53 #1
 Join Date
 Sep 2004
 Location
 Portsmouth, Hampshire, England
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
COUNTIF <> '' (2002 SP3)
Hi y'all
Apart from =SUMPRODUCT((LEN(A1:A35)<>0)*1) can anyonne think of a way (using COUNTIF maybe) that I can count the cells in a column that are not equal to ""
TIA
Regards
Paul

20060311, 22:10 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: COUNTIF <> '' (2002 SP3)
I would enter an array function:
=sum(if(A$1:A$35 <> "",1) formula has to be entered with ctrlshiftenter
since I could change the test to be = "Doug" or >5 etc. at some later date
On the other hand, if I know it is just nonblanks I would ever be worried about,
=counta(A$1:A$35)
will work, too...

20060311, 22:18 #3
 Join Date
 Sep 2004
 Location
 Portsmouth, Hampshire, England
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: COUNTIF <> '' (2002 SP3)
<hr>=sum(if(A$1:A$35 <> "",1)<hr>That is equivalent to =SUMPRODUCT((A1:A35<>"")*1) since SUMPRODUCT is an implicit array function.
<hr>=counta(A$1:A$35)<hr>won't work because the cells are equal to "" (from an if statement) rather than blank.

20060311, 23:11 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: COUNTIF <> '' (2002 SP3)
<P ID="edit" class=small>(Edited by Jezza on 11Mar06 23:11. To add PS)</P>Hi Paul
I think this is what you are wanting, I have tried it out with a range that contained a couple of if statements and some blanl cells and it seems to work:
=(ROWS(A1:A35)*COLUMNS(A1:A35))COUNTBLANK(A1:A35)
Good luck
PS
If it is just Column A with this then it can be further simplified:
=ROWS(A:A)COUNTBLANK(A:A)Jerry

20060312, 00:38 #5
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: COUNTIF <> '' (2002 SP3)
=COUNT(Range)+COUNTIF(Range,"?*")
Microsoft MVP  Excel

20060312, 00:45 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: COUNTIF <> '' (2002 SP3)
Hello Aladin,
Unless I'm mistaken, your formula doesn't take TRUE/FALSE values into account ( I assume that the original poster wants to count those as not "")

20060312, 00:57 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: COUNTIF <> '' (2002 SP3)
Hoi Hans,
That's right.
Closer to the SumProduct formula in behavior:
=COUNTA(Range)COUNTIF(Range,"")
The error values would also be counted in by the foregoing formula.
However, I just assumeed that only text and numeric values would be of interest.
AladinMicrosoft MVP  Excel

20060312, 01:05 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: COUNTIF <> '' (2002 SP3)
Yes, if only text and number values are of interest, the formula from your previous reply will do the job nicely. Otherwise, Jezza's formulas are probably best, because they cover all situations. COUNTA doesn't count really blank cells (it does count cells that are blank as the result of a formula).

20060312, 05:21 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: COUNTIF <> '' (2002 SP3)
<hr>COUNTA doesn't count really blank cells (it does count cells that are blank as the result of a formula).<hr>
They are not really blank if they contain a formula. A blank cell is an empty cell. If a formula is in a cell, it is not empty. A null string is a zero length string. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
Steve

20060312, 10:33 #10
 Join Date
 Sep 2004
 Location
 Portsmouth, Hampshire, England
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: COUNTIF <> '' (2002 SP3)
Thanks for all the replies guys.
In this application the cells that I am interested in have a formula that return text or "". Sad @#$!% that I am, I figured out whilst trying to get to sleep last night that =COUNTA(A:A,"?*") should get me what I need for this. However I shall store Jezza's and Aladin's ideas away for future use.
Thanks once again.