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

    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


    Thanks for your reply!
    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

    Pivot.xlsx 12.83 kB – 66 Downloads

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


    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. :P


    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!

Register Yourself Login