Forum Discussion
Solved
Count last 5
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
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.
2 Replies
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
