+ Reply to Thread
Results 1 to 3 of 3

How do I use DCount to exclude data?

  1. #1
    Chick N Egg
    Guest

    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

  2. #2
    Registered User
    Join Date
    03-17-2006
    Posts
    47

    dcount

    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.

    Quote Originally Posted by Chick N Egg
    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

  3. #3
    Harlan Grove
    Guest

    Re: 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)


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1