COUNTIF ” (2002 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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…

Reply | Quote

[indent]

---

=sum(if(A$1:A$35 “”,1)

---

[/indent] That is equivalent to =SUMPRODUCT((A1:A35″”)*1) since SUMPRODUCT is an implicit array function.

[indent]

---

=counta(A$1:A$35)

---

[/indent] won’t work because the cells are equal to “” (from an if statement) rather than blank.

Reply | Quote

(Edited by Jezza on 11-Mar-06 23:11. To add PS)

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)

Reply | Quote

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

Reply | Quote

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 “”)

Reply | Quote

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.

Aladin

Reply | Quote

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

Reply | Quote

[indent]

---

COUNTA doesn’t count really blank cells (it does count cells that are blank as the result of a formula).

---

[/indent]

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.

Steve

Reply | Quote

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.

Reply | Quote