DCOUNT

S

s_filman

Hi, I am trying to use DCOUNT in excel to count the number of products
sold on a particular date.

The problem I'm having is I need to add a second criteria (the date)
which is on another sheet in the booklet and I don't know the proper
syntax to make it work.

=DCOUNT(Sheet1!A2:E24,5,Sheet1!I1772:I1779)

Any ideas

Thanks
Scott
 
P

Peo Sjoblom

Just add another criteria in the criteria range like

I1772:J1772

put the header where the dates are in A2:E24 as header in J1771 and then
simply link to the date in the other sheet like

=Sheet2!A1


in J1772



--


Regards,


Peo Sjoblom
 
S

s_filman

Ok, so I have queried a database which and the data is in Sheet1, I
have my Product codes that I want in the first criteria off to the
side on sheet1. But I also have a calendar of sorts on Sheet2; this
is where the dates that I want to add to the second criteria. So how
would I link the dates (that keep changing for everyday from Jan 1 07
to Apr 15 07)

So I have my statement that will give me the number of product sold in
that entire time period but I also need to add the specific date to
the formula

Date # product sold
jan 1
jan 2
jan 3

=DCOUNT(Sheet1!A2:E24,5,Sheet1!I1772:I1779)

so the A2:E24 is the database, 5 being the "quantity" field, Sheet1!
l1772:l1779 being the first array of criteria of product codes
 
P

Peo Sjoblom

If it is a date range that you want to check against where basically you
want count if the dates in your database are between 2 dates you can use yet
another criteria field meaning one header for the first criteria you already
have and 2 headers for the date criteria then you could use something like

=">="&MIN(Sheet2!A1:A30)

for the first date criteria and

="<="&MAX(Sheet2!A1:A30)

If it is not between 2 dates but maybe 4 dates in Sheet2 then you can use

=AND(B6="x",ISNUMBER(MATCH(A6,Sheet2!A1:A10,0)))

where B6 is the first cell in the first criteria column and x the criteria
and A6 is the first date in the database and Sheet2!A1:A10 is a range of
dates


Of course you could easily use SUMPRODUCT instead of DCOUNT


=SUMPRODUCT(--(Range1="x"),--(ISNUMBER(MATCH(Range2,Sheet2!A1:A10,0))))

where Range1 is the column in the database that you already got working and
x the criteria, where Range2 is the database range with dates and
Sheet2!A1:A10 the range of dates you want to check against

Both formulas return the same result but the latter is much easier with any
archaic criteria setup


--


Regards,


Peo Sjoblom
 
S

s_filman

=SUMPRODUCT(--(Range1="x"),--(ISNUMBER(MATCH(Range2,Sheet2!
A1:A10,0))))


So "x", can that be an array. I have a number of different products
that can count towards the total count at the end of the day

the database can have 10 products codes that can count towards the
total.
 
P

Peo Sjoblom

It can be an array but not in that example, there "x" is a value or a single
cell
the range test is done in the second part of that formula. It might be
easier if you just said I have Sheet1!A2:A500 and I want to count this
criteria but only where Sheet1!B2:B500 is either this, or that or that
criteria.


--


Regards,


Peo Sjoblom
 
Top