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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
 
Master Excel Bundle

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.

Forum statistics

Threads
1,151,822
Messages
5,766,648
Members
425,366
Latest member
Mau15092000

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