Count of uniques in one column based on criteria in another column

Posted on 2014-08-13
Last Modified: 2014-08-13
Column B contains primarily 4-digit numbers stored as text, with the balance of rows that are either blank or have a single word in the cell (I don't have control of how this data is entered, unfortunately). Column C contains initials + date, with some rows that include "N/A" at the end, as shown below:

Column C
MN–18 Jun 14
MC–16 Jun 14 N/A
AC–13 May 14
MN–25 May 14 N/A

I would like a formula that returns a count of the # of unique 4-digit characters in Column B for all the rows where Column C doesn't not contain "N/A".

I've attached a sample spreadsheet.

Question by:Andreamary
    LVL 50

    Expert Comment

    by:barry houdini
    Hello Andrea,

    When you say "unique" I'm assuming you really mean "different", i.e. the number of different 4 digit codes

    I used this formula


    confirmed with CTRL+SHIFT+ENTER

    and that gave me a result of 322

    regards, barry

    Author Comment

    Hi Barry,
    Thanks for the quick response. I was verifying the result of 322 by doing this manually (filtering out the n/a's in column C, then filtering out all but the 4-digit numbers in column B, copying and pasting results of Column B into a new sheet and using "Remove duplicates") and I got 321. I did it several times, always with the same result of 321. Is there a chance the heading is being included in the formula, giving the result of 322?

    LVL 50

    Accepted Solution

    Yes, you're right, apologies - I tried adjusting that formula several different ways without success so try this different approach to get 321


    still confirmed with CTRL+SHIFT+ENTER

    regards, barry

    Author Closing Comment

    Works like a charm...thanks, Barry, much appreciated!


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    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

    23 Experts available now in Live!

    Get 1:1 Help Now