Calculate Number of Consecutive Groups of Cells from the End

[LabMadeMonk]

Hello!

I want to calculate the following for each row:
How many connected cells at the end of the row contain a character such as 'x', where cells separated by 'interval-1' empty cells are still considered in one chain and are counted. A chain of 'x' cells is only broken by 'interval' amount of consecutive empty cells.

The result should show the number of 'x' cells inside of that last chain, even if the distance between them is less than 'interval' (further apart isn't allowed, but closer together is allowed - i.e. it should count the number of values in the chain, not how many times 'interval' fits into the chain.)

My table looks something like the table below.
I highlighted the chains that are considered, and marked where chains break using a hyphen (-).


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]interval[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]x
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

I tried doing this using a separate table to the right of the original. It contains TRUE/FALSE values for each cell showing whether that cell is preceded by 'interval' number of empty cells. Each cell in the second table has a formula looking something like this (example for cell C2):
=AND(CELL("contents",C2)>0,COUNTIF(INDIRECT(ADDRESS(ROW(),COLUMN(C2)-B2)&":"&ADDRESS(ROW(),COLUMN(C2)-1)),"x")=0)

Then I find the last occurrence of such a cell (giving me the beginning of the last chain):
=SUMPRODUCT(MAX(COLUMN(C2:K2)*(TRUE=C2:K2)))

And then I simply use COUNTIF() to get the number of 'x' cells from the beginning of the chain to the end.

There is however one issue with this method - when the 'interval' is large, the first function will look at too many cells to the left including non-existing cells outside of the spreadsheet. For example, in the last column in the given table, that function will count the number of 'x' cells from two rows to the left of the sheet to row C, which is an invalid interval.

I found this thread which uses the function I might need:
https://www.mrexcel.com/forum/excel-questions/550976-counting-consecutive-cells-value.html
... But I don't know how to apply it to my situation because I wait to look for cells that can be separated and don't contain numbers

 

[Eric W]

OK, here's a non-volatile version:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]interval[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]result[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]{=IFERROR(COUNTIF(INDEX(A2:K2,1,MAX(IFERROR(IF(FREQUENCY(IF(C2:K2="",COLUMN(C2:K2)),IF((C2:L2="x")+(COLUMN(C2:L2)=COLUMN(L2)),COLUMN(C2:L2)))>B2-1,IFERROR(SMALL(IF((C2:L2="x")+(COLUMN(C2:L2)=COLUMN(L2)),COLUMN(C2:L2)),{1;2;3;4;5;6;7;8}),"")),0))):K2,"x"),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



It's pretty ugly, but seems to work. Lightly tested, it works on the sample sheet, plus some more cases I tried. Note that it requires a column (L) after the last data column. It doesn't matter what's in it, you can hide it if you want. Also the array constant from 1-8 should have as many values as columns in the data range. Frankly, I'd stick with the volatile version, unless there are some severe calculation issues on the spreadsheet.