# 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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### s-o-s

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

##### MrExcel MVP
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
114
Replies
3
Views
36
Replies
1
Views
81
Replies
0
Views
87
Replies
2
Views
214

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

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