+ Reply to Thread
Results 1 to 3 of 3

ignoring duplicates in countifs

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    winnipeg, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Wink ignoring duplicates in countifs

    this seems so simple yet I can't for life of me figure it out. I have two columns of data (see below) - I need a formula that would allow me to count the number of reps in a particular office ignoring diplicate entries (ex. the number I would be looking for in office number 1 would be 2) help please.



    Office # Rep #

    1 11111
    1 11111
    1 33333
    2 22222
    2 34568
    3 25466
    4 13546
    5 13246

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: ignoring duplicates in countifs

    Hello and welcome to the forum,

    Assuming you have headers on row 1, and your data starts from row 2 to row 9, from A1 to B9, you can try this Array formula on C2
    Please Login or Register  to view this content.
    Because it is an Array formula, when you enter it you will need to hold Ctrl-Shift and hit Enter. If you see the formula being wrapped inside a { } afterward, then you did it right.

    After that, just drag it down to C9.

    Here is a small sample with the formula. Please prepare one beforehand next time, it will be much easier for others to help you also.
    Attached Files Attached Files
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: ignoring duplicates in countifs

    Option:
    =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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