count items

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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
 

sony

Board Regular
Joined
Jun 15, 2002
Messages
126
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
Jun 15, 2002
Messages
126
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
Jun 15, 2002
Messages
126
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Forum statistics

Threads
1,144,510
Messages
5,724,774
Members
422,576
Latest member
kayth891

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top