+ Reply to Thread
Results 1 to 4 of 4

Multiple Values Corresponding Cells with Blank Entries Between

Hybrid View

  1. #1
    Registered User
    Join Date
    11-24-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    1

    Exclamation Multiple Values Corresponding Cells with Blank Entries Between

    I am trying to perform the following:
    Column A has text.
    If the text in Column A equals a specific value, "apples" for example.

    I need to count how many times in the corresponding row of Column A="apples" to multiple columns; Column C,G,K,O equaling either "Y" OR "N"

    There are blank values in the corresponding rows of Column A when it equals "apples" in Columns C,G,K,O.

    In other words, when Row 2 of Column A = apples, Row 2 Column C has blanks, Y or N.

    I'm counting repeating sequences (sequence being apples, oranges, pears) in Column A, based on Y or N in columns C,G,K,O. So for example:

    Column A ----- C ----- G ----- K ----- O
    apples ----- Y ----- ----- Y -----
    oranges ----- Y ----- N ----- -----
    pears ----- ----- Y ----- -----
    apples ----- ----- N ----- -----
    oranges ----- N ----- ----- -----
    pears ----- ----- ----- ------

    So, in total apples has a value of Y or N, 3 times. Y, 2 times and N, 1.
    The values in Column A repeat in the sequence through 150 row, and in my specific example the sequence is 32 repeating values, all specific text.

    If you have questions, please ask. I hope I've been as clear as possible to reach an answer. Of my 32 text values, I'm trying to decipher fewest entries, percentages, etc. based on these results.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple Values Corresponding Cells with Blank Entries Between

    So are you just wanting to count how many Y's are associated with Apples, and how many N's are associated with Apples through the whole list? And same for oranges and pears?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: Multiple Values Corresponding Cells with Blank Entries Between

    If what NBVC said is what you're aiming for, you could do the following:

    These formulas/values are specified to go starting in column AA for typing simplicity here. Alternatively, you can slide things down and put it on the top (recommended).
    AA2: apples
    AA3: oranges
    AA4: ...
    [all fruits in the column]

    AB1: Y
    AC1: N


    PHP Code: 
    AB2: =SUMPRODUCT(--($A$2:$A$150=$AA2),($C$2:$C$150=AB$1)+($G$2:$G$150=AB$1)+($K$2:$K$150=AB$1)+($O$2:$O$150=AB$1)) 
    Drag the formula in AB2 down for all the fruits and across. It should give you the full totals for them all.

    S

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Multiple Values Corresponding Cells with Blank Entries Between

    Or...

    If your database is in A2:E150

    and you create a table in say, H1:K4 with row headers (Apples, Oranges, Pears) in H2:H4 and column headers in I1:K1 (Yes, No, Total)

    then in I2:

    =SUMPRODUCT(($A$2:$A$7=$H2)*($B$2:$F$7=I$1))

    copied down and over 1 column.

    and in K2:

    =SUM(I2:J2)

    copied down.

+ 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