Thread: COUNTIF <> '' (2002 SP3)

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

2. Re: COUNTIF <> '' (2002 SP3)

I would enter an array function:

=sum(if(A\$1:A\$35 <> "",1) formula has to be entered with ctrl-shift-enter

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 non-blanks I would ever be worried about,

=counta(A\$1:A\$35)

will work, too...

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

4. Re: COUNTIF <> '' (2002 SP3)

<P ID="edit" class=small>(Edited by Jezza on 11-Mar-06 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)

5. Re: COUNTIF <> '' (2002 SP3)

=COUNT(Range)+COUNTIF(Range,"?*")

6. Re: COUNTIF <> '' (2002 SP3)

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 "")

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

8. 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).

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

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•