how many time does it appear

[ms5435]

i have atable of almost 6000 numbers (6x5 table). Each number is between 1-99. I would like to know how many times a number appears in the table. I don't care how the results appear.

[Anne Troy]

Suppose all those numbers will appear in Column A....

=COUNTIF(A:A,99)

or

=COUNTIF(A2:A6000,99)

[ms5435]

THE TABLE IS SET UP IN A 6 COLUNM(A-F) x 979 ROWS. There is acutually 5874 numbers. The table keeps on growing on a weekly basis.

[Aladin Akyurek]

On 2002-04-07 21:53, ms5435 wrote:
THE TABLE IS SET UP IN A 6 COLUNM(A-F) x 979 ROWS. There is acutually 5874 numbers. The table keeps on growing on a weekly basis.

Activate the option Insert|Name|Define.
Enter DataRecs as name in the Names in workbook box.
Enter as formula in the Refers to box:

MATCH(9.99999999999999E+307,x!$A:$A)

Activate Add.

Enter DATA as name in the Names in workbook box.
Enter as formula in the Refers to box:

=OFFSET(x!A2,0,0,DataRecs-1,6)

Activate OK.

Note. Change x to the name of the sheet where your numbers are.

The OFFSET formula assumes that your data grow downwards.

In column A in a different sheet,

in A2 enter: 1
in A3 enter: 2

Select A2:A3 and copy down until the number 99 appears.

In B2 enter: =COUNTIF(DATA,B2)

Double click on the fill handle of B2 in order to copy down the formula.

Aladin

[Dave Hawley]

Hi ms5435


A very simple Pivot Table based on a Dymnamic range: http://www.ozgrid.com/Excel/DynamicRanges.htm Would be ideal for this.

[Mark W.]

On 2002-04-07 21:35, ms5435 wrote:
i have atable of almost 6000 numbers (6x5 table). Each number is between 1-99. I would like to know how many times a number appears in the table. I don't care how the results appear.

Select a vertical array of 99 adjacent cells and enter the array formula...

{=FREQUENCY(range,ROW(INDIRECT("1:99")))}

...where "range" is the cell reference of your 2-dimensional table.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-04-08 07:06 ]