3 Replies Latest reply: Mar 20, 2013 2:07 AM by Barry
maltman99 Level 1 Level 1 (0 points)

I was wondering if anyone could point me in the right direction.

 

I'm looking to count the number of distinct records (text based cells) based on matching criteria from 2 or 3 other columns.

 

So I have a spreadsheet with a whole bunch of order information with one column being "end user". There are multiple orders for end users. There is another column with the "Rep" and also additional columns with "Purchase period" and "first purchase period"

 

So

Column A - Customer

Column B - Rep

Column C - Purchase period

Column D - First purchase period

 

I need to be able to count 2 things:

1. Number of distinct customers (not rows) per rep who purchased in a period -- i.e number of distinct "Customer" records based on "Rep" and "Purchase Period" or total number of customers Rep A sold to in 2010 - Q1

2. Number of new customers per rep / purchase period -- i.e number of distinct "Customer" records based on "Rep" and a correlating "Purchase Period" and first purchase period. or Total number of new customers Rep A sold to in 2010 - Q1

 

 


 

Any help you could provide would great.

 

Thanks heaps


iWork / Numbers, Mac OS X (10.7.5)
  • 1. Re: Count Distinct Items With Multiple Criteria
    Barry Level 7 Level 7 (29,180 points)

    Hi,

     

    If I've read your question correctly, this should provide the results you want for question 1:

    Picture 2.png

    The yellow filled columns are used to construct a single datum for each transaction in the selected quarter, then to count the number of distinct rep/customer transactions for each rep. These columns may be hidden.

     

    Rep names must be entered in the same order in the auxiliary columns of Table 1 as in the columns of Table 2 ("Summary"). Enter the Quarter information into cell A1 of Table 2. The entry must mathc the format used for the quarter information in Table 1.

     

    Formulas (Table 1)

     

    Columns A - D contains entered data.

     

    E2, and filled down: =IF(C=Summary :: $A$1,B&" "&A,"")

     

    F2, filled right to H2, and down: =IF(AND(LEFT($E2,LEN(F$1))=F$1,COUNTIF($E$2:$E2,"="&$E2)=1),MAX(F$1:F1)+1,"")

     

    Formula (Summary)

     

    A2, and filled right: =MAX(Table 1 :: F)

     

    Counting the number of new customers for each rep depends on how that information is recorded in the 1st period column.

     

    Regards,

    Barry

  • 2. Re: Count Distinct Items With Multiple Criteria
    maltman99 Level 1 Level 1 (0 points)

    Thanks for the input Barry -- really appreciate it mate.

     

    It won't quite solve this problem for me but could well help me with some others I'm bound to encounter.

     

    The key problem with doing it this way is the size of the dataset I'm working with -- there would be well in excess of 1000 unique customers and the number of records is in the 10's of thousands.

     

    I need to find something along the lines of pivot table style functionality that summarises according to metrics. I can do all revenue totalling stuff fine using sumifs but the counting part has me baffled. Not sure if you can do some sort of combination using count and frequency or match style functions. Wlll keep digging.

     

    Thanks mate

  • 3. Re: Count Distinct Items With Multiple Criteria
    Barry Level 7 Level 7 (29,180 points)

    You're going to find Numbers quite slow at doing anything involving a table with 'records in the 10s of thousands.'

     

    It's just not designed to handle large tables. You'll find MS Excel, or one of the open source Office applications (OpenOffice.org, LibreOffice, NeoOffice) a better tool for large datasets.

     

    Regards,

    Barry