Unique entries based on condition - count distinct

R

Rich

Hi,

I'm really struggling with what I thought should be easy.

A B C
D
branch salesperson transaction num Item code


I'm trying to the number of unique saples people in each branch, the number
of unique tranactions by each sales person, and the number of unique items
in each transaction.

In another program I use "Count Distinct" but am struggling to acheieve the
same in excel. Any ideas?
 
B

Bob Phillips

Try these array formulae

=SUM(N(FREQUENCY(IF(A1:A100="branch",MATCH(B1:B100&"",B1:B100&"",0)),MATCH(B1:B100&"",B1:B100&"",0))>0))

SUM(N(FREQUENCY(IF(B1:B100="salesperson",MATCH(C1:C100&"",C1:C100&"",0)),MATCH(C1:C100&"",C1:C100&"",0))>0))

etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
How to count the numbers in a Word document? 0
Populate empty cells to use for conversion 1
Data Lookup Question 1
Space at end of multi page list to accommodate new entries 1
Counting unique values based on 2 columns 3
count unique entries 5
Unique Value Count in 3 Columns Using Search Criteria 0

Top