0

I would like to count if in the column there are N subsequent elements of a certain value (in this example it's 1). So for the below example, it should return true for A, but false for B. Empty cells shall be ignored.

  A   B
+---+---+
| 1 | 1 |
| 1 | 1 | 
| 1 | 1 |  
|   |   |
| 1 | 2 |  
|   | 1 |
+---+---+
| Y | N | <-- RESULT
+---+---+

Ideally would be if I could mark all of those 4 or more subsequent cells.

akkonrad
  • 1,043
  • 1
  • 10
  • 25
  • So you want the result to be ```Y``` if all the cells in that column have the same value (ignoring those left blank) right? – Mateo Randwolf Jan 11 '21 at 14:24
  • not exactly - I would like to have Y if from all values in the column there is exactly N (in my case 4) that occurs after each other, at the same time ignoring empty cells. – akkonrad Jan 12 '21 at 21:52

2 Answers2

3

Have a look at this one as well - you need to pull it across for columns B, C etc.

=ArrayFormula(max(frequency(if(filter(A:A,A:A<>"")=1,sequence(count(A:A)),""),if(filter(A:A,A:A<>"")<>1,sequence(count(A:A)),"")))>=4)

enter image description here

So how does this work? It goes all the way back to @Barry Houdini, who as far as I know was the first to post this elegant method to find the longest sequence of repeated values within a range in Excel.

It uses Frequency. Frequency counts the number of values which fall within a series of bins defined by cut points

Frequency(<Values>,<Cut points>)

So if your cut points were 10,20,30 and your values were 12,15,25,35,36,37 you would expect the counts to be 2,1 and 3.

@Barry's insight was to realise that if you made the row numbers for values that you didn't want to count be the cut points and the row numbers for values you did want to count (in this case 1) be the values, then you could use Frequency to count up the number of consecutive values of interest. This led him to this formula (in his case looking for consecutive zeroes bounded by ones):

=MAX(FREQUENCY(IF((A2:A100=0)*(A2:A100<>""),ROW(A2:A100)),IF(A2:A100=1,ROW(A2:A100))))

which is easily adapted for the present situation simply by changing 0 to 1 and 1 to <>1, filtering out the blanks and using Sequence instead of row number.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

try:

=ARRAYFORMULA(IF(TRANSPOSE(MMULT(TRANSPOSE(N(REGEXMATCH(""&
 IF(A1:B6="", 1, A1:B6), "1"))), 
 SEQUENCE(ROWS(A1:B6), 1)^0))/ROWS(A1:B6)=1, "Y", "N"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124