DCOUNT

  • Thread starter Thread starter s_filman
  • Start date Start date
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
 
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
 
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
 
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
 
=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.
 
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
 
Back
Top