+ Reply to Thread
Results 1 to 4 of 4

Formula to count distinct values WITH a lookup comparison

  1. #1
    Registered User
    Join Date
    07-16-2009
    Location
    boston, ma
    MS-Off Ver
    Excel 2003
    Posts
    2

    Formula to count distinct values WITH a lookup comparison

    Hello,
    Looking for a formula (in the green box of attachment) that will give me the distinct number of "customers" in column F where the value in column E matches value in field $A$2.

    Let's say there are 200 rows. 50 rows match $A$2 to the corresponding values in column E. Out of those 50 rows there are only a total of 4 distinct customers in column F. The other 46 are duplicates.

    I want the query to return the number 4.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula to count distinct values WITH a lookup comparison

    You can use this "array formula" in B2

    =SUM(IF(FREQUENCY(IF(E$2:E$201=A2,MATCH(F$2:F$201,F$2:F$201,0)),ROW(B$2:B$201)-ROW(B$2)+1),1))

    confirm with CTRL+SHIFT+ENTER and copy down

  3. #3
    Registered User
    Join Date
    07-16-2009
    Location
    boston, ma
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Formula to count distinct values WITH a lookup comparison

    Hmm, seems to just return 1.

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Formula to count distinct values WITH a lookup comparison

    As daddylonglegs said

    confirm with CTRL+SHIFT+ENTER and copy down

    not just ENTER
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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