How do I use DCount to exclude data?
 

I am trying to exclude price ranges from <100,000 to 150,000 from my
database, my criteria range is B9:C10.
Price <100000
Price 150000
These are on 2 separate lines.

I have my database entered and "price" as my field range, but my criteria
range is wrong. I am using Office XP. How do I set this up?

T Lodge

How do I use DCount to exclude data?
 

Looking at Excel's Help File on Dcount (specifically the Criteria
Examples section), it may be that you need to have the two prices on
the same row; see the part of the Help file's example that says:
=DCOUNT(A4:E10,"Age",A1:F2) This function looks at the records of apple
trees between a height of 10 and 16 and counts how many of the Age
fields in those records contain numbers.

Chick N Egg Wrote:
I am trying to exclude price ranges from <100,000 to 150,000 from my
database, my criteria range is B9:C10.
Price <100000
Price 150000
These are on 2 separate lines.

I have my database entered and "price" as my field range, but my
criteria
range is wrong. I am using Office XP. How do I set this up?

T Lodge


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=524948

How do I use DCount to exclude data?
 

Clivey_UK wrote...
Looking at Excel's Help File on Dcount (specifically the Criteria
Examples section), it may be that you need to have the two prices on
the same row; . . .

Chick N Egg Wrote:
I am trying to exclude price ranges from <100,000 to 150,000 from my
database, my criteria range is B9:C10.
Price <100000
Price 150000
These are on 2 separate lines.

I have my database entered and "price" as my field range, but my
criteria range is wrong.

OP is correct, the criteria range is screwed up. AND criteria need to
appear in the same line in the criteria range, and the field names need
to appear in the top row of the criteria range just as they need to
appear in the top row of the data range. Also, criteria ranges specify
which records to select, NOT which records to exclude.

So the criteria range should look like

Price Price
=100000 <=150000

Alternatively, if the Price column of the data range were column G and
the first record of the data range were in row 5, the criteria could be
combined in a criteria range with a blank cell in the top row and a
formula in the second row. Like so.

<blank
=AND(G5=100000,G5<=150000)