counting function in excel

RobIC

New Member
Joined
Aug 28, 2002
Messages
3
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
3ad1
4ax2
5dc2
6ae1
7xg1
8c
9e
10g
11c
12x
Sheet1
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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