+ Reply to Thread
Results 1 to 4 of 4

Counting occurrence of values in multiple columns with one another

  1. #1
    Registered User
    Join Date
    08-19-2009
    Location
    Redwood, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Counting occurrence of values in multiple columns with one another

    Hello,
    I have 8 columns, A, B, C, D, E, F, G, and H Each column has 2669 rows.
    A1:A2669
    B1:B2669
    C1:C2669
    D1:D2669
    E1:E2669
    F1:F2669
    G1:G2669
    H1:H2669

    Each field under each column is a value that has been pasted in from another app. A through to H represents a set (meaning A1, B1, C1, D1, E1, F1, G1, and H1 are the results from one experiment; A2, B2, C2, D2, E2, F2, G2, H2 are the results from a second experiment, etc.)

    The values are numbers ranging from 1-5000. COUNTIF permits me to tally up the number of times a specific value (let's say 271) occurs in the A1:A2669 range.

    What I need to know is how many times do values occur with one another? i.e. How many times does 271 occur with 318, 599, 202, etc. in relation to all the sets?

    Thank you in advance.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting occurrence of values in multiple columns with one another

    do you mean if all 8 cells in a row = all 8 in another row in the same order?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    08-19-2009
    Location
    Redwood, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Counting occurrence of values in multiple columns with one another

    Hi,
    No. What I'm looking for is a way of determining how often a given number appears along side with other numbers.

    To simplify the typing process:
    1, 2, 3
    1, 3, 5
    1, 3, 6
    1, 7, 8

    Is there a way to have excel tell me how many times 1 appears with 3? In this example, 1 appears 4 times and it appears with 3 three times. Of course, I'd like to be able to report on 8 columns.

    thanks again

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting occurrence of values in multiple columns with one another

    Unlikely to an approach you want to use but to avoid horrendous formulae... you could adopt an approach that concatenates the values in ascending order, eg (assuming 8 columns are A:H):

    Please Login or Register  to view this content.
    You can then use an efficient and straightforward COUNTIF approach to get your count of lines where both 1 & 3 appear, eg:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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