Calculate Number of Consecutive Groups of Cells from the End

L

LabMadeMonk

New Member
Joined
Dec 23, 2018
Messages
2
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
LabMadeMonk said:
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
Click to expand...

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD]--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
ABCDEFGHIJKLMNOPQRSTUVW
1intervalresult
2Title1xxxxx2xx123x1xx2
3Title1xxxxxx0x1x1xxxx10
4Title2xxxx3x12x1x1x13
5Title2xxxx3x123x1x1x3
6Title3xxx3x12x12x123
7Title3xxxx4x12xx12x14
8Title6-xx21x12345x12
Sheet2
Cell Formulas
RangeFormula
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.


[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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
Code: 
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):
Code: 
=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
 
Upvote 0
Here's a formula version which doesn't require helper cells:

ABCDEFGHIJKLM

<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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]interval[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]result[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]x[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/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] "]L2[/TH]
[TD="align: left"]{=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")}[/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]
 
Upvote 0
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")}
 
Upvote 0
Eric W said:
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")}
Click to expand...

is there a way to do it without offset or indirect? meaning without volitile functions? thx!
 
Last edited:
Upvote 0
LabMadeMonk said:
[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]
Click to expand...
Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).
 
Upvote 0
Rick Rothstein said:
Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).
Click to expand...

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.
 
Upvote 0
Capture.png
[/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.. :(

Capture1.png
[/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
 
Upvote 0
Thank you everyone for all the replies, I will try out the solutions after Christmas/the holidays.


To answer some of the questions:


dave2018 said:
question: does your data only appear in columns C through K?
Click to expand...


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!

Rick Rothstein said:
Why does L7 have a 4 in it? I would think it should be 2 (there are 2 XBBX sequences where B means blank).
Click to expand...

Eric W said:
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.
Click to expand...


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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA - Insert rows above active cell
Replies
2
Views
57
RE Dev
R
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
644
felixstraube
F
C
  • Question Question
How would i use the FIND method with the AND operator to find any two words in a sheet cell, i.e., "understanding" AND "heart"
2
Replies
11
Views
528
Trebor76
T
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
305
John_w
J
K
  • Question Question
Return a user specified number of cell values to a comma list randomly
Replies
1
Views
324
Eric W
Eric W

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top