Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: how many time does it appear

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    =COUNTIF(A:A,99)

    or

    =COUNTIF(A2:A6000,99)
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ms5435


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



  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •