Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: help!

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to count how many times 2 particular requirements were met on a spreadsheet. For example: I have a spreadsheet of cars listed by color and price. I want to know how many red cars listed (column A) are under $5000.00 (column B). I've tried countif + countif and it just adds the totals instead of distinguishing those that meet both requirements. Any ideas? Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using Dcount
    It's never too late to learn something new.

    Ricky

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya, say your talking about through row 100:

    =SUMPRODUCT((A1:A100="red")+0,(B1:B100<50000)+0)

    Hope this helps,
    Adam

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-25 09:59, Autumn wrote:
    I need to count how many times 2 particular requirements were met on a spreadsheet. For example: I have a spreadsheet of cars listed by color and price. I want to know how many red cars listed (column A) are under $5000.00 (column B). I've tried countif + countif and it just adds the totals instead of distinguishing those that meet both requirements. Any ideas? Thanks.
    Lets say that you have the color condition in D2 and the price condition in E2. What follows would give you the desired count:

    =SUMPRODUCT((A2:A400=D2)*(B2:B400=E2))

    where A2:A400 houses the color values and B2:B400 the prices.

    Aladin


  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the suggestions. The sumproduct formula worked, my follow up question: is there a way to alter the formula so I can search all of the columns? I mean, say I have 10 columns total, 5 are color and 5 are price, can I "search" all of the columns for my 2 criteria with one formula? By chance?
    Thanks again.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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