# counting function in excel

#### RobIC

##### New Member
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?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.

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
ABCD
3CountofItems
4ItemsTotal
5a4
6c2
7d1
8e1
9g1
10x2
11GrandTotal11
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
ABCD
1ItemsItems
2aa4
4ax2
5dc2
6ae1
7xg1
8c
9e
10g
11c
12x
Sheet1

Replies
2
Views
175
Replies
5
Views
392
Replies
2
Views
279
Replies
7
Views
282
Replies
3
Views
412

1,218,596
Messages
6,143,393
Members
450,484
Latest member
ChrisMac1

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