New Contributor

# 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!

4 Replies

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

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

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

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

Slightly modified formula suggested by @Quadruple_Pawn if use structured tables

which is

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

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

in E3:

``````=LET(
filtered, FILTER(OrderData[Customer],
(OrderData[Location]=[@Location]) *
(OrderData[Product Code]=[@[Product Code]]),
0
),
IF(COUNT(filtered), 0, COUNTA(UNIQUE(filtered)))
)``````

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

Thanks so much for your help, this worked perfectly!