Counting backwards in a row until a certain number is met

[furness121]

I have an attendance streak tracker, I want it to tell me the current number of streaks for this term. (I have already calculated the highest overall number of streaks in the term and year using max & frequency).

I need it to look at the last number entered and if it is 10, count backwards until it reaches another number less than 0 in the row and then stop, if it has a number less than 10 at the start it will enter 0 as they have no streak this week.

e.g in the example data the first row should say 40 as there are 4, 10 since the last number less than 10 and the second should say 0 as that number is less than 10.

Attendance data current.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1			Autumn term										Spring term											Summer term
2	Name	Reg	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40		Current streak
3	Week number		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33
4	Student 1		10	0	10	10	10	10	0	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	4	10	10	10	10								40
5	Student 2		10	10	0	0	10	10	10	10	10	0	10	10	10	10	10	10	10	10	10	10	10	10	10	6	10	10	6								0
6	Student 3
7	Student 4
8	Student 5
9	Student 6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:AI9	Cell Value	=10	text	NO

 

[awoohaw]

I'm not sure if this is going to always work, it requires no intervening blank cells. But, it seems to work here:

UPDATE after I posted: This is sad. The TAKE, SCAN, and LAMBDA functions are only available on 365 and Excel on the web:

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web
So, unless you can do this on the web, the formula will be hard to build.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1			Autumn term										Spring term											Summer term
2	Name	Reg	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40		Current streak
3	Week number		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33
4	Student 1		10	0	10	10	10	10	0	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	4	10	10	10	10								40
5	Student 2		10	10	0	0	10	10	10	10	10	0	10	10	10	10	10	10	10	10	10	10	10	10	10	6	10	10	6								0
6	Student 3
Sheet2

Cell Formulas
Range	Formula
AK4:AK5	AK4	=TAKE(SCAN(0,FILTER(C4:AI4,C4:AI4<>"",""),LAMBDA(a,b,IF(b=10,a+b,0))),,-1)

 

[Tetra201]

The following formula returns the expected results for the posted dataset:

=10*(LOOKUP(2^1023,C4:AI4,COLUMN(C4:AI4))-LOOKUP(2^1023,1/(C4:AI4<>10)/(C4:AI4<>""),COLUMN(C4:AI4)))

 

[awoohaw]

The following formula returns the expected results for the posted dataset:

=10*(LOOKUP(2^1023,C4:AI4,COLUMN(C4:AI4))-LOOKUP(2^1023,1/(C4:AI4<>10)/(C4:AI4<>""),COLUMN(C4:AI4)))

Pretty neat @Tetra201 ! I'm happy someone was able to post a solution for the OP's version.
Best Wishes!

 

[Eric W]

Here's one that works using functions available to Excel 2021:

=MAX(0,SUM(XLOOKUP({10,9},C4:AI4,COLUMN(C4:AI4),COLUMN(B4),-1,-1)*{10,-10}))

 

[furness121]

Thank you all for your swift and helpful responses - problem solved!