how many time does it appear

ms5435

New Member
Joined
Apr 6, 2002
Messages
6
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Suppose all those numbers will appear in Column A....

=COUNTIF(A:A,99)

or

=COUNTIF(A2:A6000,99)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top