## Count Distinct Items With Multiple Criteria

446 Views 3 Replies Latest reply: Mar 20, 2013 2:07 AM by Barry
Calculating status...
Currently Being Moderated
Mar 19, 2013 9:39 PM

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

Thanks heaps

iWork / Numbers, Mac OS X (10.7.5)
• Level 7 (28,775 points)
Currently Being Moderated
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

• Level 7 (28,775 points)
Currently Being Moderated
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

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.