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...
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.
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
