1

I currently have a column of letters in B2:B11 alongside numbers that increment by 1 when letters appear consecutively (C2:C11). When a new letter appears, the sequence resets and starts from 1 again.

This is the formula I'm using:

=SCAN(0,B2:B11,
LAMBDA(a,b,
IF(OFFSET(b,-1,0)=b,
a+1,1)
)
)

It works fine when the letters are together in blocks, but when they are separated, any previous instances of a letter are forgotten about.

I want to find a solution that uses a single formula. I believe I'm on the right path using the new SCAN() function. Please don't suggest methods involving classic formulas or tables (I've already seen these).

The values returned should match those in D2:D11.

enter image description here

ZygD
  • 22,092
  • 39
  • 79
  • 102
Statto
  • 410
  • 3
  • 9
  • 1
    hi, might be of interest https://stackoverflow.com/questions/71806040/add-cells-value-until-specific-number-is-reached-in-excel – jspcal Oct 06 '22 at 22:43

2 Answers2

1

Here the array version in E2:

=COUNTIF(OFFSET(A2,0,0,SEQUENCE(ROWS(A2:A11)),1),A2:A11)

or using LET for easier maintenance:

=LET(start, A2, range, A2:A11, 
  COUNTIF(OFFSET(start,0,0,SEQUENCE(ROWS(range)),1),range))

This is the output: sample excel file

The idea was taken from here: Running Count Array Formula in Excel 365

Note: The third argument of OFFSET is optional, default is 1, so it can be omitted.

David Leal
  • 6,373
  • 4
  • 29
  • 56
0

This worked for me:

=COUNTIF(B$2:B2,"="&B2)

spreadsheet

Enigmativity
  • 113,464
  • 11
  • 89
  • 172