Count distinct function?...

[lenny3k]

Hi there.

Is there a way to count distinct values in a range of cells in a worksheet?

i'm looking for something like a COUNT DISTINCT function that would count all distinct values in an entire column. any ideas?

any help would be appreciated!

Len

 

[daveWP]

Wow, its gunna take me some time to unravel this one.

nice formula.

 

[daveWP]

Domenic,

Why did you use the tilde when you were concatenating the arrays in your Frequency argument?

 

[Domenic]

Domenic,

Why did you use the tilde when you were concatenating the arrays in your Frequency argument?

Have a look at Aladin's post in the following thread...

http://www.mrexcel.com/forum/showthread.php?t=317692&highlight=tilde

 

[Aladin Akyurek]

Let A2:C10 on Sheet1 house the relevant records.

Sheet2

A1: 1

A2: 2

B1, control+shift+enter (not just enter) and copy down:

=SUM(IF(FREQUENCY(IF(Sheet1!$A$2:$A$10=A1,
   MATCH("~"&Sheet1!$A$2:$A$10&"|"&Sheet1!$B$2:$B$10&"|"&Sheet1!$C$2:$C$10,
     Sheet1!$A$2:$A$10&"|"&Sheet1!$B$2:$B$10&"|"&Sheet1!$C$2:$C$10&"",0)),
       ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1),1))

This avoids counting possible unfortunate combinations.

If partially empty cells of a record are not allowed to count in, something like the following would be needed...

B1, control+shift+enter (not just enter) and copy down...

=SUM(IF(FREQUENCY(IF((Sheet1!$A$2:$A$10<>"")*(Sheet1!$B$2:$B$10<>"")*
    (Sheet1!$C$2:$C$10<>""),IF(Sheet1!$A$2:$A$10=A1,
   MATCH("~"&Sheet1!$A$2:$A$10&"|"&Sheet1!$B$2:$B$10&"|"&Sheet1!$C$2:$C$10,
     Sheet1!$A$2:$A$10&"|"&Sheet1!$B$2:$B$10&"|"&Sheet1!$C$2:$C$10&"",0))),
       ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1),1))

 

[brooklyn2007]

Hello everyone. Ok, this is my problem. I need to find a formula in Excel in order to Count the total number of unique Client's ID that bought product from a specific Sales Agent in a specific Month Below is and example of the data.

P.S. The blank cells have to be avoided from the Counter

Thanks


A1 B1 C1 D1
ID SalesAgent M Sold
1 t101 November 34
2 t102 November 54
3 t103 November 23
4 t104 November 11
5 t105 November 36
6 t103 October 71
1 t101 October 50
8 t102 October 53
9 t103 October 57
10 t104 October 60
11 t105 October 64
12 t101 October 67
13 t102 October 70
14 t103 October 74
15 t104 October 77
16 t101 October 81
17 t102 October 84
18 t103 October 87
19 t104 October 91
20 t105 October 94
21 t101 October 98
22 t102 October 101
23 t103 October 104
24 t104 October 108
1 t101 October 111
26 t102 October 115
27 t103 October 118
28 t104 October 121
29 t105 October 125
1 t101 October 128
31 t102 October 132
32 t103 October 135
33 t104 October 138
34 t101 October 142
35 t102 October 145
36 t103 October 149

 

[Aladin Akyurek]

Hello everyone. Ok, this is my problem. I need to find a formula in Excel in order to Count the total number of unique Client's ID that bought product from a specific Sales Agent in a specific Month Below is and example of the data.

P.S. The blank cells have to be avoided from the Counter

...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(
   IF($A$2:$A$40<>"",
   IF(($B$2:$B$40=G2)*($C$2:$C$40=H2),
   IF(ISNUMBER($D$2:$D$40),MATCH($A$2:$A$40,$A$2:$A$40,0)))),
     ROW($A$2:$A$40)-ROW($A$2)+1),1))

Assumption: The entries in the A range do not contain wild chars or other special meaning chars like <, etc.

 

[brooklyn2007]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(
   IF($A$2:$A$40<>"",
   IF(($B$2:$B$40=G2)*($C$2:$C$40=H2),
   IF(ISNUMBER($D$2:$D$40),MATCH($A$2:$A$40,$A$2:$A$40,0)))),
     ROW($A$2:$A$40)-ROW($A$2)+1),1))

Assumption: The entries in the A range do not contain wild chars or other special meaning chars like <, etc.

The formula gives me #VALUE!. H2 and G2 references are blank in my sheet. Lets say I need to find out the total number of different clients that bought from Sales Agent t104 in October. What would be the right formula?

 

[brooklyn2007]

The formula gives me #VALUE!. H2 and G2 references are blank in my sheet. Lets say I need to find out the total number of different clients that bought from Sales Agent t104 in October. What would be the right formula?

This is the uploaded file with try out formula

http://www.mediafire.com/?li789g1x37mj1kd

 

[Aladin Akyurek]

The formula gives me #VALUE!. H2 and G2 references are blank in my sheet. Lets say I need to find out the total number of different clients that bought from Sales Agent t104 in October. What would be the right formula?

Apparently, I forgat to tell you what you are supposed to have in H2:G2...

In H2 enter: t104

In G2 enter: October

I2: Type in the formula you are given and confirm the formula with control+shift+enter, not just with enter.

 

[brooklyn2007]

Apparently, I forgat to tell you what you are supposed to have in H2:G2...

In H2 enter: t104

In G2 enter: October

I2: Type in the formula you are given and confirm the formula with control+shift+enter, not just with enter.

Nope, it won't work. It shows 0 instead of 6 which is the right answer. Please check the upload file.

http://www.mediafire.com/?ka5tzl9z0vilcu9