Count Distinct Items With Multiple Criteria

Question

Level 1

0 points

Count Distinct Items With Multiple Criteria

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

User uploaded file

Any help you could provide would great.

Thanks heaps

iWork / Numbers-OTHER, Mac OS X (10.7.5)

Posted on Mar 19, 2013 9:39 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Mar 20, 2013 12:27 AM in response to maltman99

Hi,

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

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

Reply

Answer 2

maltman99 Author

Level 1

0 points

Mar 20, 2013 1:38 AM in response to Barry

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

Reply

Answer 3

Barry

Level 9

59,861 points

Mar 20, 2013 2:07 AM in response to maltman99

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

Reply