Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo

Hi Jo
If only numeric values:
=SUM(N(FREQUENCY(A2:A12,A2:A12)0))

See Chip Pearson's http://www.cpearson.com/excel/Duplicates.aspx

HTH
Cordially
Pascal


"Jo" a écrit dans le message de news:
...
Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo

Ctrl+Shift+Enter
=SUM(IF(A1:A30<"",1/COUNTIF(A1:A30,A1:A30)))
Jo wrote:
Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo

On Oct 10, 9:54 am, "papou" wrote:
Hi Jo
If only numeric values:
=SUM(N(FREQUENCY(A2:A12,A2:A12)0))

See Chip Pearson'shttp://www.cpearson.com/excel/Duplicates.aspx

HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
m...



Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo- Hide quoted text -

- Show quoted text -

What if I hide an order using "hide", how do I make your formula take
care of that?

Thanks,
Jo

Jo
Try by yourself and see what happens!
HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
...
On Oct 10, 9:54 am, "papou" wrote:
Hi Jo
If only numeric values:
=SUM(N(FREQUENCY(A2:A12,A2:A12)0))

See Chip Pearson'shttp://www.cpearson.com/excel/Duplicates.aspx

HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
m...



Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo- Hide quoted text -

- Show quoted text -

What if I hide an order using "hide", how do I make your formula take
care of that?

Thanks,
Jo

On Oct 10, 9:54 am, "papou" wrote:
Hi Jo
If only numeric values:
=SUM(N(FREQUENCY(A2:A12,A2:A12)0))

See Chip Pearson'shttp://www.cpearson.com/excel/Duplicates.aspx

HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
m...



Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo- Hide quoted text -

- Show quoted text -

Papou,

What if I "Hide" one order number, how do I make the frequency formula
working? Does SUBTOTAL take care of that? If so, what is the "function
number for that?

In the above example, if I hide order # 75, I want the answer to be 2
automatically and so on.

Thanks,
Jo

On Oct 10, 10:32 am, "papou"
wrote:
Jo
Try by yourself and see what happens!
HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
om...
On Oct 10, 9:54 am, "papou" wrote:





Hi Jo
If only numeric values:
=SUM(N(FREQUENCY(A2:A12,A2:A12)0))

See Chip Pearson'shttp://www.cpearson.com/excel/Duplicates.aspx

HTH
Cordially
Pascal

"Jo" a écrit dans le message de news:
m...

Hi,

Say you have in column A this:

Order No
24
24
24
24
123
123
123
123
123
75
75

and so on. It means that we have 3 orders (i.e., 24, 123, and 75). How
can caculate this? I mean what formula can be used to do so?

Thanks,
Jo- Hide quoted text -

- Show quoted text -

What if I hide an order using "hide", how do I make your formula take
care of that?

Thanks,
Jo- Hide quoted text -

- Show quoted text -

I tried it, the hidden ones still counted! I know SUBTOTAL take care
of that but not sure what is the function number for it?