How do you use an array formula to count the number of times SO FAR a value appears in the array up to where the value is in the array?

Question

I'm trying to use an array formula in Google Sheets in order to get how many times so far in an array a value has appeared. Specifically, I'm using an "array" of single upper case letters (themselves created using the split function), and I would be using a function to highlight the letters which are over the limit in a certain color.

For example, if a word put in was ZOOTOPIA, the result should be like so:

Z	O	O	T	O	P	I	A
1	1	2	1	3	1	1	1

I currently am using =iferror(split(regexreplace(C2, "(.)", "$1_"), "_"),"") to create the array of letters, and I had tried both =ARRAYFORMULA(if(len(D2:2), COUNTIF(D$2:2, D2), "")) - but it only ends up giving the number of times the first letter is used - and =ARRAYFORMULA(if(len(D2:2), COUNTIF(D$2:D2, D2), "")) - which itself always gives a 1

z.. · Accepted Answer · 2025-10-25 15:57:11Z

2

Another solution:

=ARRAYFORMULA(
   REDUCE(
     TOROW(, 1),
     MID(A1, SEQUENCE(1, LEN(A1)), 1),
     LAMBDA(a, c, HSTACK(a, {c; COUNTIF(INDEX(a, 1), c) + 1}))
   )
 )

First, we split the text into individual characters. Then, the REDUCE function iterates through each character, using COUNTIF to count how many times that character has already appeared.

answered Oct 25 at 15:57

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Sharpe Oct 25 at 16:37

Using reduce to generate the split array and count just what has been added to the array so far is an elegant solution.

Tom Sharpe · Accepted Answer · 2025-10-26 22:36:01Z

A lambda is probably the way to go. This just uses Choosecols to select the portion of the array up to the current character:

=let(arr,split(regexreplace(J1, "(.)", "$1_"), "_"),map(sequence(1,columns(arr)),lambda(c,COUNTIF(choosecols(arr,sequence(1,c)),index(arr,c)))))

or instead of using an array, work directly with the original word:

=map(sequence(1,len(J1)),lambda(c,c-len(substitute(left(J1,c),mid(J1,c,1),""))))

or again if you already have the letters separately starting in D2, there is an old trick that you can use:

=ArrayFormula(if(D2:2="","",countifs(D2:2,D2:2,column(D2:2),"<="&column(D2:2))))

which is similar to your formula but has an array in the criteria part of the countifs and adds a condition that the columns being counted are the current column and those to the left of it.

If your goal is to highlight separate letters where they exceed a certain frequency the first time and subsequent times they occur, then the custom formula in conditional formatting would just be (e.g. for frequency greater than one):

=countif($D2:D2,D2)>1

Collectives™ on Stack Overflow

How do you use an array formula to count the number of times SO FAR a value appears in the array up to where the value is in the array?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related