I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record is
blank.

If I do the exact same query except have nothing in the criteria cell, it
counts all records. I think the problem is that DCounta does not an empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

Why not just use

=COUNTIF(C:C,J4)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record is
blank.

If I do the exact same query except have nothing in the criteria cell, it
counts all records. I think the problem is that DCounta does not an empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

I am trying to have the ability for the users to choose to have multiple
criteria. For example, dropdown for Salesperson, Date, Process Step . . .

I'm not sure that COUNTIF function can use multiple criteria?

"Bob Phillips" wrote:

Why not just use

=COUNTIF(C:C,J4)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record is
blank.

If I do the exact same query except have nothing in the criteria cell, it
counts all records. I think the problem is that DCounta does not an empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

No but SUMPRODUCT can

=SUMPRODUCT(--(rng1=value1),--(rng2="value2"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dcwood57" wrote in message
...
I am trying to have the ability for the users to choose to have multiple
criteria. For example, dropdown for Salesperson, Date, Process Step . . .

I'm not sure that COUNTIF function can use multiple criteria?

"Bob Phillips" wrote:

Why not just use

=COUNTIF(C:C,J4)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record
is
blank.

If I do the exact same query except have nothing in the criteria cell,
it
counts all records. I think the problem is that DCounta does not an
empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

What's the problem, assume that the database was A4:A20 and there were 5
empty cells
and J4 is empty, what do you expect the formula to return? Also are the
blanks in the database empty cells or are they blanks from formulas like "",
if the latter DCOUNTA will count all cells
if not it will ignore it. You can use a formula like this instead

=SUMPRODUCT(--(A5:A20=J4))

will count A5:A20 using what's in J4


--


Regards,


Peo Sjoblom




"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record is
blank.

If I do the exact same query except have nothing in the criteria cell, it
counts all records. I think the problem is that DCounta does not an empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

The advantage of using DCountA() is that you are able to use multiple
criteria. For example, I want to count all the instances in the database
where it is between dates A & B AND Process Step 1 was accomplished. If I
leave the "SalesRep" field blank, I want it to count ALL rows where Process
Step 1 was accomplished, regardless of whether or not there was a salesrep
assigned.

Is there a place where we can post examples? It is very simple to show the
problem, simpler than to explain it.

"Peo Sjoblom" wrote:

What's the problem, assume that the database was A4:A20 and there were 5
empty cells
and J4 is empty, what do you expect the formula to return? Also are the
blanks in the database empty cells or are they blanks from formulas like "",
if the latter DCOUNTA will count all cells
if not it will ignore it. You can use a formula like this instead

=SUMPRODUCT(--(A5:A20=J4))

will count A5:A20 using what's in J4


--


Regards,


Peo Sjoblom




"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record is
blank.

If I do the exact same query except have nothing in the criteria cell, it
counts all records. I think the problem is that DCounta does not an empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

You can use multiple criteria as well with SUMPRODUCT, it's much more
versatile than the archaic D functions

=SUMPRODUCT(--(A1:A50=J4),--(B1:B50=DATE(2007,1,15)),--(B1:B50<=DATE(2007,1,31)))

--


Regards,


Peo Sjoblom



"dcwood57" wrote in message
...
The advantage of using DCountA() is that you are able to use multiple
criteria. For example, I want to count all the instances in the database
where it is between dates A & B AND Process Step 1 was accomplished. If I
leave the "SalesRep" field blank, I want it to count ALL rows where
Process
Step 1 was accomplished, regardless of whether or not there was a salesrep
assigned.

Is there a place where we can post examples? It is very simple to show
the
problem, simpler than to explain it.

"Peo Sjoblom" wrote:

What's the problem, assume that the database was A4:A20 and there were 5
empty cells
and J4 is empty, what do you expect the formula to return? Also are the
blanks in the database empty cells or are they blanks from formulas like
"",
if the latter DCOUNTA will count all cells
if not it will ignore it. You can use a formula like this instead

=SUMPRODUCT(--(A5:A20=J4))

will count A5:A20 using what's in J4


--


Regards,


Peo Sjoblom




"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record
is
blank.

If I do the exact same query except have nothing in the criteria cell,
it
counts all records. I think the problem is that DCounta does not an
empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

I can see that I am definitely going to have to investigate SUMPRODUCT more.
Thanks for the direction, this may work.

"Peo Sjoblom" wrote:

You can use multiple criteria as well with SUMPRODUCT, it's much more
versatile than the archaic D functions

=SUMPRODUCT(--(A1:A50=J4),--(B1:B50=DATE(2007,1,15)),--(B1:B50<=DATE(2007,1,31)))

--


Regards,


Peo Sjoblom



"dcwood57" wrote in message
...
The advantage of using DCountA() is that you are able to use multiple
criteria. For example, I want to count all the instances in the database
where it is between dates A & B AND Process Step 1 was accomplished. If I
leave the "SalesRep" field blank, I want it to count ALL rows where
Process
Step 1 was accomplished, regardless of whether or not there was a salesrep
assigned.

Is there a place where we can post examples? It is very simple to show
the
problem, simpler than to explain it.

"Peo Sjoblom" wrote:

What's the problem, assume that the database was A4:A20 and there were 5
empty cells
and J4 is empty, what do you expect the formula to return? Also are the
blanks in the database empty cells or are they blanks from formulas like
"",
if the latter DCOUNTA will count all cells
if not it will ignore it. You can use a formula like this instead

=SUMPRODUCT(--(A5:A20=J4))

will count A5:A20 using what's in J4


--


Regards,


Peo Sjoblom




"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the record
is
blank.

If I do the exact same query except have nothing in the criteria cell,
it
counts all records. I think the problem is that DCounta does not an
empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.

I forgot to say that the formula I posted will count what's in J4 in A1:A50
where the dates in B1:B50 are between Jan 15 07 and Jan 31 07


--


Regards,


Peo Sjoblom


"dcwood57" wrote in message
...
I can see that I am definitely going to have to investigate SUMPRODUCT
more.
Thanks for the direction, this may work.

"Peo Sjoblom" wrote:

You can use multiple criteria as well with SUMPRODUCT, it's much more
versatile than the archaic D functions

=SUMPRODUCT(--(A1:A50=J4),--(B1:B50=DATE(2007,1,15)),--(B1:B50<=DATE(2007,1,31)))

--


Regards,


Peo Sjoblom



"dcwood57" wrote in message
...
The advantage of using DCountA() is that you are able to use multiple
criteria. For example, I want to count all the instances in the
database
where it is between dates A & B AND Process Step 1 was accomplished.
If I
leave the "SalesRep" field blank, I want it to count ALL rows where
Process
Step 1 was accomplished, regardless of whether or not there was a
salesrep
assigned.

Is there a place where we can post examples? It is very simple to show
the
problem, simpler than to explain it.

"Peo Sjoblom" wrote:

What's the problem, assume that the database was A4:A20 and there were
5
empty cells
and J4 is empty, what do you expect the formula to return? Also are
the
blanks in the database empty cells or are they blanks from formulas
like
"",
if the latter DCOUNTA will count all cells
if not it will ignore it. You can use a formula like this instead

=SUMPRODUCT(--(A5:A20=J4))

will count A5:A20 using what's in J4


--


Regards,


Peo Sjoblom




"dcwood57" wrote in message
...
I'm trying to create multiple queries for users using dropdown
lists.

I use the formula =IF(ISBLANK(J4),"","="&J4) in the criteria range.

However, Dcounta does not count the record if the field in the
record
is
blank.

If I do the exact same query except have nothing in the criteria
cell,
it
counts all records. I think the problem is that DCounta does not an
empty
string "" the same as a true blank cell.

Anybody have any suggestions on how to work around this?

Thanks.