Prev: How do I make sorts default to using the header row?
Next: generating random numbers without repeats
From: Rich on 1 May 2010 03:47
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?

--
Rich
http://www.rhodes-lindos.co.uk
http://www.rhodes-pefkos.co.uk



From: Bob Phillips on 1 May 2010 05:19
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.

--

HTH

Bob

"Rich" <REPLACEWITHrich(a)richdavies.com> wrote in message
news:X%QCn.107521$iL1.106393(a)newsfe24.iad...
> 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?
>
> --
> Rich
> http://www.rhodes-lindos.co.uk
> http://www.rhodes-pefkos.co.uk
>
>
>


 | 
Pages: 1
Prev: How do I make sorts default to using the header row?
Next: generating random numbers without repeats