+ Reply to Thread
Results 1 to 3 of 3

Counting the number of times a range of values appears in a table.

  1. #1
    Registered User
    Join Date
    09-12-2010
    Location
    new orleans
    MS-Off Ver
    excel 2010
    Posts
    1

    Unhappy Counting the number of times a range of values appears in a table.

    Hey all, hopefully someone can help. This should be easy but is kicking my butt

    The reason for this is that it appears that we have some people fudging measurments over the past several years of data and that it may be ongoing. If they are not fudging then we have some external factor seeming to create trends in our data that we have to identify as some of it is too uniform.

    I need to find several things and I believe it all hinges on the counting the number of times a list of numbers appears in a table of data. Pivot tables are not working for me on this.

    The data is in Column A to Column G in my worksheet
    The columns are headed Date, M1, M2, M3, M4, M5, M6
    The Date column contains the DAY and DATE both as text. (wednesday, march 16, 1993)
    Columns headed M1 through M5 contain the numbers 1 through 80
    Column headed M6 contains the numbers 1 through 60
    Each row of data contain unique numbers in M1 through M5 (columns B,C,D,E, and F)

    The first thing I need to figure out is how to count and output every number in the range 1 to 80 appears in columns M1 through M5. so in the entire range of data in columns headed M1 to M5 how many times does 1 appear, 2 appear, 3 appear, etc all the way through 80.

    There are thousands of rows of data (and it grows every day) so doing COUNTIF for each of the 80 numbers as individual entries is not practical.

    So the first question is how do I pass the numbers 1 to 80 to the function and out put them to a new column or table?

    What i need is to create a table (or set of columns) that say "Measurment X appears" ?? "times" and automatically updates as I enter each new data row to the table. This is for each of the 80 possible numbers.

    Next I need to count the number of times that each number 1 through 50 occurs in Column G (headed M6) and that updates automatically as I enter each new data row.

    I also have to calculate how often the numbers appear in 'groups'.
    Meaning how often does any specific number appear in the same row as any other specific number or numbers.
    for example
    how often do 1, 34 and 56 appear in the same row, this has to be for all possible 5 number combinations of 1 through 80.
    No numbers in columns headed M1 through M5 will be duplicated on the same row, but the value in column headed M6 might duplicate on the same row with a number form M1 to M5. For example M3 might have 24 and M6 might also have 24 on the same row.

    Then I have two more calculations to do.
    The first is using the DAY values Monday through Sunday (7 days) how often does each of those numbers occur on each day. For example on Mondays the number 15 occured 29 times in the range of data. I need this for every number in M1 to M5 (1 to 80) for every DAY Monday through Sunday. I also need this for MG.

    Second, the 'groups' found in the previous calculations have to be checked against the same DAY frequency calculations. For example if a group (1 and 56, and 72) occurs more than ONCE in M1 to M5 how often did it occur on Mon, Tues, Weds, etc.

    I appreciate any input I can get as even the first step has me stumped at this point.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Counting the number of times a range of values appears in a table.

    Please upload example workbook (I've stop reading after Hey all )

    An explanation and general input(s) and desired output(s) would help a lot.

  3. #3
    Registered User
    Join Date
    09-18-2010
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Counting the number of times a range of values appears in a table.

    I agree with zbor, you'd definitely want to upload a sample workbook and try to explain as clearly as you can.

    I'm not sure what exactly you're talking about, but would using the subtotal option and utilizing COUNT help at all?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1