+ Reply to Thread
Results 1 to 7 of 7

Count equal and adjacent cells

  1. #1
    Registered User
    Join Date
    10-18-2020
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    1

    Count equal and adjacent cells

    Hi everyone.

    I've been struggling with something that I think must be quite simple but I'm overthinking it.

    I need to count how many sequential times a group of conditions repeat. In the example bellow you may see what I'm talking about:

    [table="width: 500"]
    [tr]
    Date Location Type Result
    01/10/2020 1/20 Test Conform
    02/10/2020 2/35 Test Conform
    03/10/2020 2/25 Test Non-conform
    04/10/2020 1/20 Repetition Conform
    05/10/2020 1/25 Test Non-conform
    06/10/2020 1/25 Repetition Non-conform
    07/10/2020 1/25 Test Conform
    08/10/2020 1/20 Test Non-conform
    09/10/2020 1/20 Repetition Non-conform
    10/10/2020 1/20 Test Non-conform
    11/10/2020 1/35 Test Non-conform
    20/10/2020 1/25 Test Non-conform
    20/10/2020 1/20 Test Conform

    I need excel to count how many non-conform tests occur per location but the "Repetition" type don't count, the counting is only if two sequential test are non-conform and if a result, for instance, for location 1/20 is "non-conform", then the next test for this location is "conform" the counting should reset.

    I tried to create a dynamic table for this and it helped me visualize how many sequential values are "non-conform" but I can't seem to figure how to count them only if the are adjacent to each other.

    Please help!!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: Count equal and adjacent cells

    I do not follow your explanation. Please amend your sheet to show your expected reults (manual calculation)... and try again to explain how you get those results.
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: Count equal and adjacent cells

    On second thoughts... a guess. In F2:
    =IFERROR(INDEX($B$2:$B$14,MATCH(0,INDEX(COUNTIF($F$1:$F1,$B$2:$B$14),0),0)),"")

    and in G2:
    =IF(F2="","",SUMPRODUCT(($B$2:$B$13=F2)*($B$3:$B$14=F2)*($D$2:$D$13="Non-conform")*($D$3:$D$14="Non-conform")))
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,831

    Re: Count equal and adjacent cells

    Formula for E2 and then fill down =MAX(IF(ISERROR(FIND(REPT("Non-conform,",ROW($L$1:$L$20000)),TEXTJOIN(",",TRUE,IF($B$1:$B$20000=B2,D2,"")),1)),0,ROW($L$1:$L$2000)))
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  5. #5
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    838

    Re: Count equal and adjacent cells

    OP Shows Excel 2013, which doesn't have TEXTJOIN.

  6. #6
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,831

    Re: Count equal and adjacent cells

    Thanks. My Mistake.

  7. #7
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    838

    Re: Count equal and adjacent cells

    This can be done by adding more columns to your table in Folha1!A1:D14.

    E2: #N/A
    E3: =MATCH(1/(C3="Test"),0/(B$2:B2=B3)/(C$2:C2="Test"))

    Fill E3 down into E4:E14.

    F2: =COUNTIFS(C2,"Test",D2,"Non-conform")
    F3: =IF(COUNTIFS(C3,"Test",D3,"Non-conform"),1+IFERROR(INDEX(F$2:F2,E3),0),0)

    Fill F3 down into F4:F14.

    E2:E14 are the row indices in A2:D14 at which the most recent previous instance for the same location in column B and Test appeared in column C. F2:F14 are the consecutive counts down to that row.

    These produce the following results.

    A
    B
    C
    D
    E
    F
    1
    Date Local Type Result
    2
    2020-10-01 1/20 Test Conform #N/A 0
    3
    2020-10-02 2/35 Test Conform #DIV/0! 0
    4
    2020-10-03 2/25 Test Non-conform #N/A 1
    5
    2020-10-04 1/20 Repetition Conform #DIV/0! 0
    6
    2020-10-05 1/25 Test Non-conform #N/A 1
    7
    2020-10-06 1/25 Repetition Non-conform #DIV/0! 0
    8
    2020-10-07 1/25 Test Conform 5 0
    9
    2020-10-08 1/20 Test Non-conform 1 1
    10
    2020-10-09 1/20 Repetition Non-conform #DIV/0! 0
    11
    2020-10-10 1/20 Test Non-conform 8 2
    12
    2020-10-11 1/35 Test Non-conform #N/A 1
    13
    2020-10-20 1/25 Test Non-conform 7 1
    14
    2020-10-20 1/20 Test Conform 10 0

    Meaning the only consecutive Test/Non-conform entries for the same location are in rows 9 and 11 for location 1/20. You'd need another column of additional formulas to determine maximum consecutive numbers before resets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. conditional format equal, adjacent cells
    By Emma Eve in forum Excel General
    Replies: 6
    Last Post: 02-21-2019, 01:26 PM
  2. counting in a data set if adjacent cells are equal
    By ed nott in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2018, 07:55 AM
  3. Replies: 2
    Last Post: 09-11-2017, 03:42 PM
  4. [SOLVED] count number of cells in Column C that have a lesser or equal value to the adjacent cell
    By Geoff Jones in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-18-2013, 11:01 AM
  5. [SOLVED] SUM cells with equal values in an adjacent column.
    By angelopc in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-13-2013, 09:55 AM
  6. Counting adjacent cells(text) to equal one value
    By Calithea in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2012, 04:08 AM
  7. ►[SOLVED] by Richard Buttrey - Message Box Error when Adjacent Cells Equal
    By xsoldoutx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-13-2012, 08:07 AM

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