Formula in DCounta Criteria

[dcwood57]

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.

 

[Bob Phillips]

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)

 

[Peo Sjoblom]

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]

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?

 

[dcwood57]

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]

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]

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

 

[Peo Sjoblom]

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

 

[Bob Phillips]

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]

Thanks very much for your help.

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]

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]

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]

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