Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

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.

Formula in DCounta Criteria
 

Thanks very much for your help.

"Peo Sjoblom" wrote:

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.

Formula in DCounta Criteria
 

I'm still having some difficulty with this. What I'm trying to accomplish is
this - If the user does not specify a salesperson, calculates on the entire
database. If the user does specify a salesperson, selects and calculates on
just the salesperson selected.

Using SUMPRODUCT, if I leave the criteria blank, it counts only the
instances where salesperson is not entered. Is it possible for SUMPRODUCT to
ignore a criteria if it is blank? Or possibly to have a wildcard? Could I
use something like "*" in the criteria field, rather than a blank?

"Peo Sjoblom" wrote:

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.

Formula in DCounta Criteria
 

Assume your database would be 10 rows only, how many columns are there, what
do each column contain. Where do you put the criteria and what are the
different criteria.

For instance if you leave a criteria cell empty you could always check that
first with IF, assume we use J4 and what do you want to COUNT if J4 is
blank, nothing?

=IF(J4="",0,SUMPRODUCT(....)



--


Regards,


Peo Sjoblom


"dcwood57" wrote in message
...
I'm still having some difficulty with this. What I'm trying to accomplish
is
this - If the user does not specify a salesperson, calculates on the
entire
database. If the user does specify a salesperson, selects and calculates
on
just the salesperson selected.

Using SUMPRODUCT, if I leave the criteria blank, it counts only the
instances where salesperson is not entered. Is it possible for SUMPRODUCT
to
ignore a criteria if it is blank? Or possibly to have a wildcard? Could
I
use something like "*" in the criteria field, rather than a blank?

"Peo Sjoblom" wrote:

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.

Formula in DCounta Criteria
 

The database has 20+ columns. Many of the columns are process steps - True /
False, but some contain SalesRep, Customer Name, Phone #, that sort of thing
- strings.

If J4 is empty, I want the criteria to be IGNORED -

Lets assume that J4 is the selector for SalesRep.

I want to query that Column C=TRUE and J4 is empty, the return should be the
count of all records where C=True, regardless SalesRep, or full department.
But if J4 is NOT empty, I wish to query for column C=True and column SalesRep
= J4= "Sam Smith".

DCountA may be archaic, but it did allow for this type of query fairly
simply. The only problem is in the fact that a formula like
=IF(IsBlank(j4),"",J4) actually returns an empty string rather than a true
blank cell. The Criteria Range for querying does not treat "" empty strings
the same as truly blank cells. In trying to build a UI wherein the user
could make a few inputs (from dropdown lists) we can do a wide variety of
queries. For example Process A as % of Total. Process B as % of A.

It may be that SUMPRODUCT is the way to go, but I'm afraid that the formulae
may be so complex as to be almost hopelessly impossible to debug.

Don

"Peo Sjoblom" wrote:

Assume your database would be 10 rows only, how many columns are there, what
do each column contain. Where do you put the criteria and what are the
different criteria.

For instance if you leave a criteria cell empty you could always check that
first with IF, assume we use J4 and what do you want to COUNT if J4 is
blank, nothing?

=IF(J4="",0,SUMPRODUCT(....)



--


Regards,


Peo Sjoblom


"dcwood57" wrote in message
...
I'm still having some difficulty with this. What I'm trying to accomplish
is
this - If the user does not specify a salesperson, calculates on the
entire
database. If the user does specify a salesperson, selects and calculates
on
just the salesperson selected.

Using SUMPRODUCT, if I leave the criteria blank, it counts only the
instances where salesperson is not entered. Is it possible for SUMPRODUCT
to
ignore a criteria if it is blank? Or possibly to have a wildcard? Could
I
use something like "*" in the criteria field, rather than a blank?

"Peo Sjoblom" wrote:

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.