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.

3. 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")))

4. 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)))

5. Re: Count equal and adjacent cells

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

6. Re: Count equal and adjacent cells

Thanks. My Mistake.

7. 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.

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

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