Count the non-numeric value starting with "T" based on specific date

[ExcelNewbie2020]

i have a long range of dates.. i want to count all the non-numeric values starting with the latest "T" upto specific date (Q2).
However the count is zero if the specific date is "T" followed by a numeric value or a numeric value.. see below

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2																	05-03-25
3
4	01-03-25	02-03-25	03-03-25	04-03-25	05-03-25	06-03-25	07-03-25	08-03-25	09-03-25	10-03-25	11-03-25	12-03-25	13-03-25	14-03-25	15-03-25		Expected Result
5	8	T	V	F	T	8	8	8	8	8	T	S	V	F	E		0	Since March 5 is T and followed by numeric values
6	6	6	T	F	E	F	S	T	F	F	E	F	S	V	X		3	Starting March 3 "T" to March 5
7	8	T	S	F	F	E	F	S	V	V	F	E	T	S	V		4	Starting March 2 "T" to March 5
8	8	8	8	T	E	F	E	F	S	V	T	E	F	S	V		2	Starting March 4 "T" to March 5
9	8	8	8	8	8	8	T	F	S	V	T	E	F	S	V		0	Since March 5 numeric values
10	8	8	8	8	T	Z	F	F	S	V	T	E	F	S	V		1	Since March 5 is "T" followed by non-numeric values
Sheet2

 

[kvsrinivasamurthy]

try, In P5 copied down

=LET(dt,$A$5:$O$10,a,MATCH($Q$2,$A$4:$O$4,0),ip,A5:INDEX(A5:O5,a),b,MAX(("T"=ip)*COLUMN(ip)),c,IF(b<a,SUM(1*(ISTEXT(INDEX(ip,b):INDEX(ip,a)))),IF(b=a,1*ISTEXT(INDEX(dt,ROWS($P$5:$P5),a+1)),1)),c)

 

[ExcelNewbie2020]

try, In P5 copied down

=LET(dt,$A$5:$O$10,a,MATCH($Q$2,$A$4:$O$4,0),ip,A5:INDEX(A5:O5,a),b,MAX(("T"=ip)*COLUMN(ip)),c,IF(b<a,SUM(1*(ISTEXT(INDEX(ip,b):INDEX(ip,a)))),IF(b=a,1*ISTEXT(INDEX(dt,ROWS($P$5:$P5),a+1)),1)),c)

thanks man, really appreciate..
it works on March 5.. but when i switch the date March 9, the first row should return to zero.. if its numeric value or "T" followed by number it should be zero..

 

[Georgiboy]

Here's another option:

=LET(fr,FILTER(A5:O5,$A$4:$O$4<=$Q$2+1),a,--ISTEXT(DROP(fr,,XMATCH("T",fr,,-1))),IF(TAKE(a,,1)=0,0,SUM(a)))

 

[fahad_ibnfurjan]

hhhhhh.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2																	3/5/2025
3
4	3/1/2025	3/2/2025	3/3/2025	3/4/2025	3/5/2025	3/6/2025	3/7/2025	3/8/2025	3/9/2025	3/10/2025	3/11/2025	3/12/2025	3/13/2025	3/14/2025	3/15/2025		Expected Result
5	8	T	V	F	T	8	8	8	8	8	T	S	V	F	E		0	Since March 5 is T and followed by numeric values
6	6	6	T	F	E	F	S	T	F	F	E	F	S	V	X		3	Starting March 3 "T" to March 5
7	8	T	S	F	F	E	F	S	V	V	F	E	T	S	V		4	Starting March 2 "T" to March 5
8	8	8	8	T	E	F	E	F	S	V	T	E	F	S	V		2	Starting March 4 "T" to March 5
9	8	8	8	8	8	8	T	F	S	V	T	E	F	S	V		0	Since March 5 numeric values
10	8	8	8	8	T	Z	F	F	S	V	T	E	F	S	V		1	Since March 5 is "T" followed by non-numeric values
Sheet1

Cell Formulas
Range	Formula
Q5:Q10	Q5	=IF((SUM(($A5:$O5="T")*($A$4:$O$4<=$Q$2))=0)+(SUM(($A$4:$O$4=$Q$2+1)*ISNUMBER($A5:$O5))=1)<>0,0,$Q$2-MAX($A$4:$O$4*($A5:$O5="T")*($A$4:$O$4<=$Q$2))+1)

 

[fahad_ibnfurjan]

S5:S10=

IF(MAX(SUM(($A$4:$O$4=$Q$2+1)*ISNUMBER($A5:$O5))*($A$4:$O$4=$Q$2)*($A5:$O5="T")),"Since March 5 is T and followed by numeric values",IF(MAX(SUM(($A$4:$O$4=$Q$2+1)*ISTEXT($A5:$O5))*($A$4:$O$4=$Q$2)*($A5:$O5="T")),"Since March 5 is ""T"" followed by non-numeric values",IF(MAX(($A5:$O5="T")*($A$4:$O$4<$Q$2)),"Starting March 3 ""T"" to "&TEXT(INDEX($A$4:$O$4,MAX((COLUMN($A$4:$O$4)-COLUMN($A$4)+1)*(($A5:$O5="T")*($A$4:$O$4<$Q$2)))),"mmm dd"),IF(MAX(ISNUMBER($A5:$O5)*($A$4:$O$4=$Q$2))=1,TEXT(INDEX($A$4:$O$4,1,MAX((COLUMN($A$4:$O$4)-COLUMN($A$4)+1)*(($A5:$O5<>"T")*($A$4:$O$4=$Q$2)))),"mmm dd")&"numeric values"))))

 

[ExcelNewbie2020]

Here's another option:

=LET(fr,FILTER(A5:O5,$A$4:$O$4<=$Q$2+1),a,--ISTEXT(DROP(fr,,XMATCH("T",fr,,-1))),IF(TAKE(a,,1)=0,0,SUM(a)))

thank you.. really appreciate the effort..
when i change the date to March 10 rows 8 to 10 counted as zero. it appears it returns 0 if the next day (March 11) is a "T"..

 

[ExcelNewbie2020]

hhhhhh.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2																	3/5/2025
3
4	3/1/2025	3/2/2025	3/3/2025	3/4/2025	3/5/2025	3/6/2025	3/7/2025	3/8/2025	3/9/2025	3/10/2025	3/11/2025	3/12/2025	3/13/2025	3/14/2025	3/15/2025		Expected Result
5	8	T	V	F	T	8	8	8	8	8	T	S	V	F	E		0	Since March 5 is T and followed by numeric values
6	6	6	T	F	E	F	S	T	F	F	E	F	S	V	X		3	Starting March 3 "T" to March 5
7	8	T	S	F	F	E	F	S	V	V	F	E	T	S	V		4	Starting March 2 "T" to March 5
8	8	8	8	T	E	F	E	F	S	V	T	E	F	S	V		2	Starting March 4 "T" to March 5
9	8	8	8	8	8	8	T	F	S	V	T	E	F	S	V		0	Since March 5 numeric values
10	8	8	8	8	T	Z	F	F	S	V	T	E	F	S	V		1	Since March 5 is "T" followed by non-numeric values
Sheet1

Cell Formulas
Range	Formula
Q5:Q10	Q5	=IF((SUM(($A5:$O5="T")*($A$4:$O$4<=$Q$2))=0)+(SUM(($A$4:$O$4=$Q$2+1)*ISNUMBER($A5:$O5))=1)<>0,0,$Q$2-MAX($A$4:$O$4*($A5:$O5="T")*($A$4:$O$4<=$Q$2))+1)

thanks.. when i change the date to March 10, range A5:O5 counted as 6 it should be zero because cell J5 is a numeric value..

 

[Georgiboy]

How about this option:

=LET(fr,FILTER(A5:O5,$A$4:$O$4<=$Q$2+1),c,IF(TAKE(fr,,-1)="T",HSTACK(DROP(fr,,-1),"E"),fr),a,--ISTEXT(DROP(c,,XMATCH("T",c,,-1))),IF(TAKE(a,,1)=0,0,SUM(a)))

 

[kvsrinivasamurthy]

Try. In P5 copied down

=LET(dt,$A$5:$O$10,a,MATCH($Q$2,$A$4:$O$4,0),ip,A5:INDEX(A5:O5,a),b,MAX(("T"=ip)*COLUMN(ip)),c,IF(b<a,SUM(1*(ISTEXT(INDEX(ip,b+1):INDEX(dt,ROWS($P$5:$P5),a+1)))),IF(b=a,1*ISTEXT(INDEX(dt,ROWS($P$5:$P5),a+1)),1)),c)