I want to see how many times each number occurs in an array.

I use Office 2003.
I have an array of six columns by various rows. anywhere from one to 600
rows. Columns will alyaws be six. The data are integers between one and 40.
Any integer may appear in any position in the array. I would like a report
that shows how many times each integer appears. I have taken all the
training that seemed relevant and bought the book "Inside Excell 2003", but
can't seem to find specific solution. It's probably really simple and I'm
just overlookeng it.

Nel post news:[email protected]
*eingram* ha scritto:

> I use Office 2003.
> I have an array of six columns by various rows. anywhere from one to
> 600 rows. Columns will alyaws be six. The data are integers between
> one and 40. Any integer may appear in any position in the array. I
> would like a report that shows how many times each integer appears.
> I have taken all the training that seemed relevant and bought the
> book "Inside Excell 2003", but can't seem to find specific solution.
> It's probably really simple and I'm just overlookeng it.

Hi eingram,

On a new sheet put this formula in A1 and copy down for 40 rows

=ROW(A1)

in this way you have numbers from 1 to 40. In B1 put the formula:

=COUNTIF(the_range_of_your_first_array,$A1)

and fill down till B40. Copy the range B1:B40 in the columns C, D, E, F and
G, changing the range of the array.

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Assuming that A2:F600 contains the data, let H2:H41 contain the numbers
1 through 40, then enter the following formula in I2 and copy down:

=COUNTIF($A$2:$F$600,H2)

Also, since you're using Excel 2003, convert the data into a list...

Data > List > Create List

The range for the formulas will automatically adjust as data is added or
removed.

Hope this helps!

In article <[email protected]>,
eingram <[email protected]> wrote:

> I use Office 2003.
> I have an array of six columns by various rows. anywhere from one to 600
> rows. Columns will alyaws be six. The data are integers between one and 40.
> Any integer may appear in any position in the array. I would like a report
> that shows how many times each integer appears. I have taken all the
> training that seemed relevant and bought the book "Inside Excell 2003", but
> can't seem to find specific solution. It's probably really simple and I'm
> just overlookeng it.

First take the six columns and combine them into one long column. Put a
header cell at the top of the column.

Then create a Pivot table to show the count of each item in the new table.

For Pivot Table info see:

http://www.peltiertech.com/Excel/Pivots/pivottables.htm

An alternative is to use 40 COUNTIF formulae.
--
Gary's Student


"eingram" wrote:

> I use Office 2003.
> I have an array of six columns by various rows. anywhere from one to 600
> rows. Columns will alyaws be six. The data are integers between one and 40.
> Any integer may appear in any position in the array. I would like a report
> that shows how many times each integer appears. I have taken all the
> training that seemed relevant and bought the book "Inside Excell 2003", but
> can't seem to find specific solution. It's probably really simple and I'm
> just overlookeng it.