count items

[sony]

dear,
do you know what excel function can count the items in a column. But if those items happen more than 1 time, it will still count 1. e.g.

column A
A
B
C
C
C
D

so , the function will retun 4 because there are only A,B,C,D inthe column
pls assist. tks

 

[Sir Vili]

Hi sony.

This code counts C -letters:

=SUMPRODUCT(($A$1:$A$6="C")*($A$1:$A$6="C"))

This also:

=SUMPRODUCT(($A$1:$A$6="C")*1)

BRGDS Sir Vili.
This message was edited by Sir Vili on 2002-10-04 03:54

 

[Andrew Poulsom]

See:

http://j-walk.com/ss/pup/pup5/functions.htm

 

[sony]

Vili,
tks. But the problem is that those items are not necessarily "C" only. It could be anything, no specific duplicate i can know in advance. I want a function which could count 1 time only for those items which happen more than one time in the column.
any such formula?

 

[Aladin Akyurek]

On 2002-10-04 03:44, sony wrote:
dear,
do you know what excel function can count the items in a column. But if those items happen more than 1 time, it will still count 1. e.g.

column A
A
B
C
C
C
D

so , the function will retun 4 because there are only A,B,C,D inthe column
pls assist. tks

Let A2:A7 house the sample.

Use one of:

(1)

Array-enter:

=SUM(IF(LEN(A2:A7),1/COUNTIF(A2:A7,A2:A7)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

(2) Download and install the morefunc.xll add-in (http://longre.free.fr/english/index.html) and use the ordinary and efficient formula...

=COUNTDIFF(A2:A7)

 

[sony]

andrew,

tks, i just browsed it. Could you tell which function in the pack i should use?

actually, i wanna a formula from existing excel function, any can meet my requirement?
pls advise tks....

 

[Aladin Akyurek]

On 2002-10-04 04:03, sony wrote:
andrew,

tks, i just browsed it. Could you tell which function in the pack i should use?

actually, i wanna a formula from existing excel function, any can meet my requirement?
pls advise tks....

See my reply which contains the most effective unique item counting formulas to date...

 

[sony]

Aladin,
yes, I see it. Thank you a lot.

 

[sony]

Aladin,
one further question,

i want to use your for formula
{sum(if(len(xx), a/countif (xx,xx)))}
i wan tot set my own formula in the pivot table formula calculated field, but it looks the formula cannot be input at there.

pls advise.

tks

 

[Aladin Akyurek]

On 2002-10-04 05:57, sony wrote:
Aladin,
one further question,

i want to use your for formula
{sum(if(len(xx), a/countif (xx,xx)))}
i wan tot set my own formula in the pivot table formula calculated field, but it looks the formula cannot be input at there.

pls advise.

tks

Your stylized version above shows "a"... That should be 1.

Regarding your current question: It's a formula that returns a scalar result (just a single number). It could be kept separate from the pivot table calculations. If you'd want to include in the pivot table, you need to add a new column to your source data area with heading, e.g., UniqueCount.

Let column A house the items that you want to compute a unique count for...

In the new column enter and copy down:

=IF(LEN(A2),1/COUNTIF($A$2:$A$100,A2),"")

Include the new column in the source data for your pivot table. You'll need the sum of the new field. Is that what you want?