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

A

B

C

D

E

F

G

H

I

J

K

L

1

interval

result

2

Title

1

x

x

x

-

x

x

2

3

Title

1

x

x

x

x

x

x

-

0

4

Title

2

x

-

x

x

x

3

5

Title

2

x

-

x

x

x

3

6

Title

3

x

x

x

3

7

Title

3

x

x

x

x

4

8

Title

6

-

x

x

2

<tbody>
</tbody>

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

 

[dave2018]

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

If you are using a table (meaniing u can spare some "real estate" on your sheet), this is what i came up with...
still trying to figure out a better way to do this, and in 1 cell...

question: does your data only appear in columns C through K? my formula is based on that...

--Copy N2 to the right until V2 and then down to V8. then copy W2 down to W8. it should work. pls test it out by adding x's and seeing if it adjusts... Excel 2016 (Windows) 32 bit A B C D E F G H I J K L M N O P Q R S T U V W 1 interval result 2 Title 1 x x x x x 2 x x 1 2 3 x 1 x x 2 3 Title 1 x x x x x x 0 x 1 x 1 x x x x 1 0 4 Title 2 x x x x 3 x 1 2 x 1 x 1 x 1 3 5 Title 2 x x x x 3 x 1 2 3 x 1 x 1 x 3 6 Title 3 x x x 3 x 1 2 x 1 2 x 1 2 3 7 Title 3 x x x x 4 x 1 2 x x 1 2 x 1 4 8 Title 6 - x x 2 1 x 1 2 3 4 5 x 1 2 Sheet2 Cell Formulas Range Formula N2 =IF(C2="x","x",IF(M2="x",1,1+M2)) W2 {=COUNTIF(V2:INDEX(N2:V2,IFNA(MATCH(9.99E+307,IF(N2:V2=B2,999,FALSE)),1)),"x")} Press CTRL+SHIFT+ENTER to enter array formulas.

<tbody>
</tbody>

 

[Anthony47]

Another possible approach is via a User Defined Function:
-From Excel, press Alt-F11 to open the editor of the macros
-Menu /Insert /Module to create a new vba "module"
-copy the following code and paste it to the vba module

Function LMM(ByVal myInt As Long, ByRef myRow As Range) As Long
Dim I As Long, BCnt As Long, lmmCnt As Long
For I = myRow.Columns.Count To 1 Step -1
    If IsEmpty(myRow.Cells(1, I)) Then
        BCnt = BCnt + 1
        If BCnt >= myInt Then
            Exit For
        End If
    Else
        BCnt = 0
        lmmCnt = lmmCnt + 1
    End If
Next I
LMM = lmmCnt
End Function

-then return to the worksheet and you can use the LMM function in a formula such as (in L2):

=LMM(B2,C2:K2)

-copy the formula downward as necessary

The first parameter (B2) is the "interval", the second one is the range with the data

Bye

 

[Eric W]

Here's a formula version which doesn't require helper cells:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		interval										result
2	Title	1	x	x				x		x	x	2
3	Title	1	x		x		x	x	x	x		0
4	Title	2	x			x		x		x		3
5	Title	2	x				x		x		x	3
6	Title	3	x			x			x			3
7	Title	3	x			x	x			x		4
8	Title	6		x						x		2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula L2 {=COUNTIF(INDEX(A2:K2,1,MAX(IF(SUBTOTAL(3,OFFSET($C2,0,COLUMN(OFFSET(C2,0,0,1,COLUMNS(C2:K2)-B2+1))-COLUMN(C2),,B2))=0,COLUMN(OFFSET(C2,0,0,1,COLUMNS(C2:K2)-B2+1))))):K2,"x")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Eric W]

A slightly more succinct way to write the formula:

L2: {=COUNTIF(INDEX(C2:K2,1,MAX(IF(SUBTOTAL(3,OFFSET(C2,0,ROW(INDIRECT("1:"&COLUMNS(C2:K2)-B2+1))-1,,B2))=0,ROW(INDIRECT("1:"&COLUMNS(C2:K2)-B2+1))))):K2,"x")}

 

[dave2018]

A slightly more succinct way to write the formula:

L2: {=COUNTIF(INDEX(C2:K2,1,MAX(IF(SUBTOTAL(3,OFFSET(C2,0,ROW(INDIRECT("1:"&COLUMNS(C2:K2)-B2+1))-1,,B2))=0,ROW(INDIRECT("1:"&COLUMNS(C2:K2)-B2+1))))):K2,"x")}

is there a way to do it without offset or indirect? meaning without volitile functions? thx!

 

[Rick Rothstein]

A

B

C

D

E

F

G

H

I

J

K

L

1

interval

result

2

Title

1

x

x

x

-

x

x

2

3

Title

1

x

x

x

x

x

x

-

0

4

Title

2

x

-

x

x

x

3

5

Title

2

x

-

x

x

x

3

6

Title

3

x

x

x

3

7

Title

3

x

x

x

x

4

8

Title

6

-

x

x

2

<tbody>
</tbody>

Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).

 

[Eric W]

Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).

As I understand it, counting from right to left, count all of the x's until you find a gap of at least the interval length. So on row 7, there are 2 gaps of length 2, but none of 3, so we count all the x's on the line.


Dave2018, if you want a formula sans INDIRECT, post #4 has that. If you want one without OFFSET too, that's a tougher nut to crack. I'm working on one using FREQUENCY, but it will probably end up being long, complicated and almost un-maintainable. I'm about ready to sign off for the day, but if I get it working, I'll update this thread. Perhaps one of the other contributors can figure something out.

 

[dave2018]

[/URL][/IMG]

in this image, i replicated my helper table with an array formula: IF((IF(B2:J2="x",1,0)=1)*(IF(C2:K2="x",1,0)=0),1,IF(C2:K2="",COLUMN(C2:K2)-1-MATCH(TRUE,C2:K2="",0),"x"))

i'm thinking along this line... but im not getting it fully right... as seen on the results of the later rows.. (as seen for example the number 8 which appears at the bottom of column V..

[/URL][/IMG]
in this image, u can see my logic in the 2 IF functions (multiplying each other for AND logic..) where the red circled IF finds the Blanks (i know its a bit backward, the way its written...) * the blue circled IF - which looks at the PREVIOUS 1 cell, relative to the cells we want [Meaning startin from B2...] - to determine if the previous cell was an X, which we then want to restart the count of consecutive blanks.

I hope i'm clear enough...

once I can replicate the table properly, i can drop it into my original formula, without the helper table.

whew...

PS. this one is so challenging for me, because u need sort of a running total BASED on previous cells - in 1 cell with an array formula, which just calculates each item of the array independent of it's neighbors...

Thx all

 

[LabMadeMonk]

Thank you everyone for all the replies, I will try out the solutions after Christmas/the holidays.


To answer some of the questions:

question: does your data only appear in columns C through K?

My table is bigger and has some more complications so I simplified it here, my post was already getting long. I'll adapt your solution to my table. Thanks for the reply!

Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).

As I understand it, counting from right to left, count all of the x's until you find a gap of at least the interval length. So on row 7, there are 2 gaps of length 2, but none of 3, so we count all the x's on the line.

Eric explains it well.
The interval number can be understood as "there has to be an x every [this many] cells, or more often." Then count all the x's from right to left, until there is too big of a gap between them.


Happy holidays if you celebrate them!