Countng the number of non blank fields in a table

[John Baker]

Hi:

I have a table, and have simple question:

I wish to count the number of entries (i.e non blank records in the table)



=DCOUNT(A3:A80,1,D67)

where d6:d7 say :

File Name
isblank()

I have also tried >0 and a number of other formulations but non appears to work.

Suggestions please.

John Baker

 

[Frank Kabel]

Hi John
one way:
=COUNTA(A3:A80)

 

[John Baker]

Excellent!

Thank You

John Baker

 

[kkknie]

Another less elegant response. Frank's answer is much mor
appropriate.

=COUNTIF(H1:J5,"<>")

Essentially, count if the cell is not equal to nothing.

 

[Frank Kabel]

Hi
I would strongly recommend not to use this syntax
You may run into trouble using this (COUNTIF has problems with the used
range in combination with this comparison).

To show this error try the following:
- open a new, frsh workbook
- enter the following formula in B1:
=COUNTIF(A1:A5;"<>")

the expected result would be '0'. Though this generate for me the
result of '1'!

 

[JE McGimpsey]

Frank - what version of XL? (I've never seen this error in MacXL)

Does it occur in any useful context (i.e, where there is more than just
the one formula in a sheet)?

 

[Bob Phillips]

Frank,

Never seen this, and it doesn't happen on my laptop, XL2000, XP Pro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi JE
Excel 2003. We had a discussion some weeks ago about COUNTIF (and
SUMIF) failures using conditions like <> or =""
Ken Wright produced the same kind of error in his Excel 2000 version.

See the discussion at
http://tinyurl.com/3csbb

 

[Frank Kabel]

Hi
tried in in Excel 2000 and Excel 2003 with fresh workbooks.
If you like I can email you my sample file

 

[Tom Ogilvy]

Countif restricts itself to the usedrange. This should be well known - it
is one of the advantages of countif - but it can be misused.


=COUNTIF(A:A,"<>")

works fine since any cell with a value will be in the used range. For
Frank's example, it returns 0 for me and I believe Frank has used the wrong
example or misunderstood the consideration.

=Countif(A:A,"=")
could be problematic (depends on your expectation) since it would only
return blank cells in the used range. It depends on your expectation.

 

[Frank Kabel]

Countif restricts itself to the usedrange. This should be well known
- it is one of the advantages of countif - but it can be misused.


=COUNTIF(A:A,"<>")

works fine since any cell with a value will be in the used range. For
Frank's example, it returns 0 for me and I believe Frank has used the
wrong example or misunderstood the consideration.

Hi Tom
you're right, error in my worksheet

Frank