+ Reply to Thread
Results 1 to 3 of 3

Count Unique Values in one Column based on a condition in another column-Please help!

  1. #1
    Registered User
    Join Date
    07-04-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    1

    Count Unique Values in one Column based on a condition in another column-Please help!

    I need some urgent help. I need to find the number of unique agencies below for number 1, but I need to exclude the agency named "HUMA" from the results. In this case, the answer would be 1(ie. Agency SOAP). What's the formula to do so? Assume the column "Number" is column A and column "Agency" is column B.

    Number Agency
    1 SOAP
    2 BALL
    1 HUMA
    2 NIJA
    2 POIS
    1 SOAP

    Thank you!

  2. #2
    Registered User
    Join Date
    07-28-2013
    Location
    Eastern Seaboard, North America
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Count Unique Values in one Column based on a condition in another column-Please help!

    One could use two COUNTIF formulas, with one COUNTIF formula subtracting the result for the item you need to exclude.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Count Unique Values in one Column based on a condition in another column-Please help!

    Welcome to the forum.

    Either of these formulae should do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Important: these are array formulae so need to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.

    Change the ranges to match your actual data, of course

    Hope that helps.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. count values from one column based on condition, with array
    By Siegfrid15 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2017, 01:22 AM
  2. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  3. Counting unique values based on condition in a different column
    By rafuk73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 02:59 PM
  4. Replies: 4
    Last Post: 07-16-2013, 09:45 AM
  5. give a count of values based on a condition in different column
    By ashdrago in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 11:13 AM
  6. Count Unique Values based on another column
    By Ineedhelp12 in forum Excel General
    Replies: 2
    Last Post: 07-02-2009, 06:12 PM
  7. Count unique values based on another column
    By verdugan in forum Excel General
    Replies: 4
    Last Post: 07-02-2009, 12:21 PM

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