# count unique occurrences with criteria referring two tables

• I'm storing data in two tables (TblOrder and TblClient).

TblOrder
OrderNo Client Date
1 A 2010
2 B 2010
3 A 2010
4 C 2010
5 D 2011
6 E 2011

TblClient
Client Country
A A
B B
C A
D A
E B

I'm trying to find out how to find the number of unique clients that placed an Order during a specific Date (from TblOrder) and are from a specific country (from TblClient).

I.e.
The number of UNIQUE clients that placed an order during 2010 AND are from country A?
Answer: equals to 2 unique clients.

Best regards,
Fredrik

## Files

• Re: count unique occurrences with criteria referring two tables

Excel 2010, Tables
With drop-down criteria lists.
Show all results and count them.
With macro.
http://c3017412.r12.cf0.rackcdn.com/05_23_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

• Re: count unique occurrences with criteria referring two tables

It would be a simple formula if you added a lookup column to one table.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Re: count unique occurrences with criteria referring two tables

I don't want to add any column to the tables, so I'm looking for a formula that will be able to refer to the tables to solve the above.
I know this would be easily done with sql, but I'm trying to find a solution with a formula like sumproduct, vlookup etc..

/Fredrik

• Re: count unique occurrences with criteria referring two tables

Denormalise your two tables into one table (or use a simple vlookup into the country table) and use a Pivot Table. Thats what Pivot Tables are good at... summarising data.

See attached.

Ger

## Files

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: count unique occurrences with criteria referring two tables

If your client table is sorted alphabetically, then you can use something like this array formula:
=COUNT(1/FREQUENCY(IF(D7:D12&LOOKUP(TblOrder[Client],TblClient)=F16&F17,MATCH(C7:C12,C7:C12,0),""),IF(D7:D12&LOOKUP(TblOrder[Client],TblClient)=F16&F17,MATCH(C7:C12,C7:C12,0),"")))
where F16 contains year and F17 contains country.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Re: count unique occurrences with criteria referring two tables

Amazing, Rory!
Thank you for your help, know I just get to dive into the formula and try to understand it!

All the best,
Fredrik

• Re: count unique occurrences with criteria referring two tables

(Or you could use a Pivot table without any formulas..... Formulas like that are just too hard to maintain, but nice job nonetheless rory.)

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!