count if difficulties

Posted on 2011-10-06
Last Modified: 2012-05-12
I want to count if in Range A2:A5000 the cell does not contain any value from the range B2:B20

any thoughts?

Using excel 2007

Question by:KnutsonBM
    LVL 50

    Expert Comment

    by:barry houdini
    Hello Brandon,

    Try this


    That will coount all cells in the range A2:A5000 that don't match any value in B2:B20 - is that what you need?

    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    Of course you normally use an equals sign at the start, i.e.


    That formula will count blanks too, if you don't want that then modify like this


    LVL 6

    Author Comment

    that will get to if it doesn't match, but i am looking for if it doesn't contain any value from B2:B20

    say Cell B2 = V01.1

    I want to count if the cells in range A2:A5000 do NOT contain that value

    given the set below it would return 1

    abc V01.1 defg
    V01.1 abc defg
    abc defg
    LVL 6

    Author Comment

    capitalized the wrong word lol, do not CONTAIN that value
    LVL 50

    Accepted Solution

    OK, can't help thinking there's an easier way....but this should do it


    confirmed with CTRL+SHIFT+ENTER

    regards, barry
    LVL 9

    Assisted Solution

    To simplify the problem, I would do as follows:

    (1) We add a new column range D2:D20 (This range shadows range B2:B20)

    (2) Paste the formula below in D2 and copy it to range D3:D20

    =IF(ISNUMBER(MATCH( B2,$A$2:$A$5000,0)),1,"")

    Above formula will display "1" in any cell of range D2:D20 if the
    corresponding cell row of range B2:B20 has a match in range A2:A5000

    (3) In Range A:5002 or whichever cell you choose, enter the formula as below:


    I used "X" as the criteria to COUNTIF, but use whatever..


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now