# count items

#### sony

##### Board Regular
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

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?

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.

=COUNTDIFF(A2:A7)

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?

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?

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

yes, I see it. Thank you a lot.

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.

tks

On 2002-10-04 05:57, sony wrote:
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.

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?

Replies
5
Views
234
Replies
3
Views
273
Replies
8
Views
282
Replies
4
Views
181
Replies
36
Views
1K

1,218,515
Messages
6,142,918
Members
450,455
Latest member
Handienak

### 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.

### Which adblocker are you using?

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

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