SOLVED

Count last 5

New Contributor

I would like a formula that counts the last 5 leters in the same column

ex:

   a b c d e f

1 q g y

2 g e h

3 h g v

4 t  l  t

5 y t  c

6 c w h

7 d r h

8 h g a

9 j  h a

 

result for column c

a 2

h 2

c 1

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@tonystony_4me  here is the formula you can use (assuming you have excel 365 sub)

=LET(n,$B$12,c,$B$13,t,$A$1:$G$10,letters,OFFSET(t,ROWS(t)-ROW(INDEX(t,1,))-n+1,MATCH(c,INDEX(t,1,),0)-1,n,1),ltrs,UNIQUE(letters),counts,COUNTIF(letters,ltrs),CHOOSE({1,2},ltrs,counts))

where B12 is the # of rows to count, B13 is the column header to check and A1:G10 is the table/range.

See attached sheet.

Thanks mtarler