Forum Discussion
How to search on one column and then count distinct entries in another column (Excel)
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
| Location | Product Code | Description |
| Location A | Product 1 | Car |
| Location B | Product 1 | Car |
| Location A | Product 2 | Bike |
| Location B | Product 2 | Bike |
Order Data Table
| Location | Product Code | Order number | Customer |
| Location A | Product 1 | 01234 | Customer X |
| Location A | Product 1 | 01235 | Customer X |
| Location A | Product 1 | 01236 | Customer Y |
| Location A | Product 2 | 01237 | Customer X |
| Location B | Product 1 | 01238 | Customer 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!
in E3:
=LET( filtered, FILTER(OrderData[Customer], (OrderData[Location]=[@Location]) * (OrderData[Product Code]=[@[Product Code]]), 0 ), IF(COUNT(filtered), 0, COUNTA(UNIQUE(filtered))) )Slightly modified formula suggested by OliverScheurich if use structured tables
which is
=LET( f, FILTER( Orders[Customer], ( Orders[Location] = [@Location] ) * ( Orders[Product Code] = [@[Product Code]] ) ), COUNTA( UNIQUE( f ) ) )=COUNTA(UNIQUE(FILTER($I$2:$I$6,$G$2:$G$6=A9)))Maybe with this formula for the layout of the attached file.
- Thanks so much for your help, this worked perfectly!
