counting function in excel

[RobIC]

Hello,
I am new to this forum and so I hope this message won't be out of place. I have a list of values in a single column, these values are repeated a varying number of times. I would like to produce a list of these values with the number of occurences of each value.

Can anyone help?

 

[s-o-s]

On 2002-08-29 04:38, RobIC wrote:
Hello,
I am new to this forum and so I hope this message won't be out of place. I have a list of values in a single column, these values are repeated a varying number of times. I would like to produce a list of these values with the number of occurences of each value.

Can anyone help?

Welcome to the board Rob,

If you have your list in column A, rows 2 -100

the following function in column B will give you the number of recurrences.

=SUMPRODUCT(($A$2:$A$100=A2)*1)

Copy this from cell B2 down to the bottom of your list.

Hope this helps.
Sean.

 

[Aladin Akyurek]

On 2002-08-29 04:38, RobIC wrote:
Hello,
I am new to this forum and so I hope this message won't be out of place. I have a list of values in a single column, these values are repeated a varying number of times. I would like to produce a list of these values with the number of occurences of each value.

Can anyone help?

Two options.

a) Construct/build a pivot table of your data.

Let A1:A12 house the following sample:

{"Items";"a";"a";"a";"d";"a";"x";"c";"e";"g";"c";"x"}

Drag Items to the ROW area.
Drag Items once more to the DATA area.

Book1
	A	B	C	D
3	CountofItems
4	Items	Total
5	a	4
6	c	2
7	d	1
8	e	1
9	g	1
10	x	2
11	GrandTotal	11
Sheet4

b) Build a unique list of items in C from C1 on, using Advanced Filter with the Unique records only option checked.

You should get the following in C1:C7 regarding the sample above:

{"Items";"a";"d";"x";"c";"e";"g"}

In D2 enter & copy down:

=COUNTIF($A$2:$A$12,C2)

See the figure...

aaCounting.xls
	A	B	C	D
1	Items		Items
2	a		a	4
3	a		d	1
4	a		x	2
5	d		c	2
6	a		e	1
7	x		g	1
8	c
9	e
10	g
11	c
12	x
Sheet1