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.
|