Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2007

Tip: Looking for answers? Try searching our database.

DCOUNT

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
s_filman@yahoo.ca - 14 Sep 2007 16:23 GMT
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
Peo Sjoblom - 14 Sep 2007 17:22 GMT
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

Signature

Regards,

Peo Sjoblom

> Hi, I am trying to use DCOUNT in excel to count the number of products
> sold on a particular date.
[quoted text clipped - 9 lines]
> Thanks
> Scott
s_filman@yahoo.ca - 14 Sep 2007 17:52 GMT
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
Peo Sjoblom - 14 Sep 2007 18:15 GMT
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

Signature

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
[quoted text clipped - 16 lines]
> so the A2:E24 is the database, 5 being the "quantity" field, Sheet1!
> l1772:l1779  being the first array of criteria of product codes
Peo Sjoblom - 14 Sep 2007 18:25 GMT
> Both formulas return the same result but the latter is much easier with
> any archaic criteria setup

Without any archaic criteria setup
s_filman@yahoo.ca - 14 Sep 2007 19:22 GMT
=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.
Peo Sjoblom - 14 Sep 2007 21:24 GMT
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.

Signature

Regards,

Peo Sjoblom

> =SUMPRODUCT(--(Range1="x"),--(ISNUMBER(MATCH(Range2,Sheet2!
> A1:A10,0))))
[quoted text clipped - 4 lines]
> the database can have 10 products codes that can count towards the
> total.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.