[SOLVED] Trying to find a way to work out current scoring sequence that needs to cross reference with number of games played by team

Trying to find a way to work out current scoring sequence that needs to cross reference with number of games played by team

I have a list of scorers and how many times they score in each match.

Is there a way to find out the current scoring streak depending on how many games their team has played?

text

The entries in column H are the expected return. More columns will appear between columns F and G.

Row 3 - Player's team have played 2 games so he is on a 2 game streak Row 9 - Player's team have played 3 games and he didn't score in game 3, so his streak is 0

Googling for similar questions, couldn't find any. I can work out the best total of consecutive games scores in, but not a current total.

Solution

Assuming "games played" prioritizes early games (i.e. "k games played" means "the first k games were played"), you could try something like this:

=LEN(REGEXEXTRACT(JOIN(,FILTER(IF(COUNTIF(C3:E3,1,0),SEQUENCE(1,COLUMNS(C3:E3))<=K3)),"1+$"))

Or with a single formula:

=MAP(SEQUENCE(ROWS(C3:E)),LAMBDA(i,LET(
   r,INDEX(C3:E,i),
   IF(COUNTA(r)=0,,
     LEN(IFNA(REGEXEXTRACT(JOIN(,
       FILTER(IF(r,1,0),SEQUENCE(1,COLUMNS(r))<=INDEX(K3:K,i))),"1+$")))))))