How to search on one column and then count distinct entries in another column (Excel)

%3CLINGO-SUB%20id%3D%22lingo-sub-3359586%22%20slang%3D%22en-US%22%3EHow%20to%20search%20on%20one%20column%20and%20then%20count%20distinct%20entries%20in%20another%20column%20(Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3359586%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20compare%20sales%20data%20from%20two%20tables.%20The%20first%20table%20shows%20product%20info%20(Location%2C%20product%20code%20etc.)%2C%20and%20the%20second%20table%20shows%20all%20transaction%20data%20(Location%2C%20product%20code%2C%20customer%20info%20for%20each%20transaction).%3C%2FP%3E%3CP%3EI%20want%20to%20look%20at%20each%20product%2Blocation%20in%20the%20first%20table%2C%20then%20find%20all%20instances%20of%20it%20in%20the%20second%20table%2C%20and%20count%20the%20number%20of%20distinct%20customers%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%20data%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProduct%20Data%20Table%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CSTRONG%3ELocation%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EProduct%20Code%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CSTRONG%3EDescription%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EProduct%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ELocation%20B%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EProduct%201%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ECar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EProduct%202%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EBike%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2230px%22%3ELocation%20B%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3EProduct%202%3C%2FTD%3E%3CTD%20height%3D%2230px%22%3EBike%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOrder%20Data%20Table%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2263.999772045493785%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3ELocation%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EProduct%20Code%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EOrder%20number%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CP%3E%3CSTRONG%3ECustomer%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EProduct%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E01234%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECustomer%20X%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3BProduct%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E01235%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECustomer%20X%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%26nbsp%3BProduct%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E01236%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECustomer%20Y%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ELocation%20A%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EProduct%202%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E01237%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECustomer%20X%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ELocation%20B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EProduct%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E01238%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ECustomer%20X%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%26nbsp%3B%20I%20want%20to%20know%20the%20number%20of%20distinct%20customers%20who%20ordered%20Product%201%20from%20Location%20A%2C%20which%20is%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20any%20help%20I%20can%20get!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3359586%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3359773%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20on%20one%20column%20and%20then%20count%20distinct%20entries%20in%20another%20column%20(Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3359773%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387333%22%20target%3D%22_blank%22%3E%40Rachel1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(FILTER(%24I%242%3A%24I%246%2C%24G%242%3A%24G%246%3DA9)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula%20for%20the%20layout%20of%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3360597%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20on%20one%20column%20and%20then%20count%20distinct%20entries%20in%20another%20column%20(Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387333%22%20target%3D%22_blank%22%3E%40Rachel1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESlightly%20modified%20formula%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3Bif%20use%20structured%20tables%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20725px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370946i745751000EA63077%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhich%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20f%2C%20%20FILTER(%0A%20%20%20%20%20%20%20%20%20Orders%5BCustomer%5D%2C%0A%20%20%20%20%20%20%20%20(%20Orders%5BLocation%5D%20%3D%20%5B%40Location%5D%20)%20*%0A%20%20%20%20%20%20%20%20(%20Orders%5BProduct%20Code%5D%20%3D%20%5B%40%5BProduct%20Code%5D%5D%20)%20)%2C%0A%20%20COUNTA(%20UNIQUE(%20f%20)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3360779%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20on%20one%20column%20and%20then%20count%20distinct%20entries%20in%20another%20column%20(Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387333%22%20target%3D%22_blank%22%3E%40Rachel1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370956i5A4B1BBD73847C5E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EE3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20filtered%2C%20FILTER(OrderData%5BCustomer%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(OrderData%5BLocation%5D%3D%5B%40Location%5D)%20*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(OrderData%5BProduct%20Code%5D%3D%5B%40%5BProduct%20Code%5D%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%200%0A%20%20%20%20)%2C%0A%20%20%20%20IF(COUNT(filtered)%2C%200%2C%20COUNTA(UNIQUE(filtered)))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3386598%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20on%20one%20column%20and%20then%20count%20distinct%20entries%20in%20another%20column%20(Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3386598%22%20slang%3D%22en-US%22%3EThanks%20so%20much%20for%20your%20help%2C%20this%20worked%20perfectly!%3C%2FLINGO-BODY%3E
New Contributor

I am trying to compare sales data from two tables. The first table shows product info (Location, product code etc.), and the second table shows all transaction data (Location, product code, customer info for each transaction).

I want to look at each product+location in the first table, then find all instances of it in the second table, and count the number of distinct customers 

 

Example data:

 

Product Data Table

LocationProduct CodeDescription
Location AProduct 1Car
Location BProduct 1Car
Location AProduct 2Bike
Location BProduct 2Bike

 

Order Data Table

LocationProduct CodeOrder number

Customer

Location AProduct 101234Customer X
Location A Product 101235Customer X
Location A Product 101236Customer Y
Location AProduct 201237Customer X
Location BProduct 101238Customer X

 

In this example,  I want to know the number of distinct customers who ordered Product 1 from Location A, which is 2.

 

I would appreciate any help I can get!

4 Replies

@Rachel1994 

=COUNTA(UNIQUE(FILTER($I$2:$I$6,$G$2:$G$6=A9)))

Maybe with this formula for the layout of the attached file. 

@Rachel1994 

Slightly modified formula suggested by @Quadruple_Pawn if use structured tables

image.png

which is

=LET(
  f,  FILTER(
         Orders[Customer],
        ( Orders[Location] = [@Location] ) *
        ( Orders[Product Code] = [@[Product Code]] ) ),
  COUNTA( UNIQUE( f ) ) )

@Rachel1994 

 

_Screenshot.png

in E3:

=LET(
    filtered, FILTER(OrderData[Customer],
                     (OrderData[Location]=[@Location]) *
                     (OrderData[Product Code]=[@[Product Code]]),
                     0
    ),
    IF(COUNT(filtered), 0, COUNTA(UNIQUE(filtered)))
)
Thanks so much for your help, this worked perfectly!