How to define blanks as DCOUNT criteria?

[Frank Krogh]

How should a DCOUNT criteria be defined that count blanks - on numeric as
well as alphanumeric values?

Thanks.


Frank Krogh

 

[Tom Ogilvy]

what do you mean by count blanks

if you mean blank cells, how are then numeric or alphanumeric

if you mean there are embedded spaces within a series of characters, then
they are only alphanumeric.

 

[Frank Krogh]

Sorry!

I just ment blank cells . In the excel help menu I see that the way to
define blanks when using Autofilter is = "=", but this does not work when
defined as a criteria range.


Frank Krogh

 

[Tom Ogilvy]

Header1
="="

worked for me for cells that are actually blank.

 

[Frank Krogh]

How is that possible? I don't understand why it doesn't work when I try the
this:

I have defined the function

=DCOUNT(myRange;"Item";myCriteria)

myCriteria is then defined like range A1:A2
Item
="="

Then I've entered this list as myRange (B1:B5)

Item (B1)
4
(1 blanks in B3)
3
2 (B5)

The DCOUNT function result is 0, but should be 1.

Can you explain what is wrong?


Regards,
Frank Krogh

 

[Tom Ogilvy]

I had the same problem as you. So I tried:

=DCOUNT(MyRange,,MyCriteria)

and it worked for me

You have to think this one through

Dcount, like count, only counts the cells with numbers (if the field
argument is specified). when you put the critiera in to only look at cells
that are blank in the column Item, and then try to count in the same column
(the second argument to Dcount), your count has to be zero - blank cells
don't contain numbers.

So why does the formula with no second argument work.

in help it says:

If field is omitted, DCOUNT counts all records in the database that match
the criteria.

so Dcount is performing a different function if the field is not specified.
It counts the number of records that meet the criteria.