Count if consecutive value is repeated 3 times.

[leehy1]

Hi All,

I need help writing a formula, I've googled alot and feel like I'm getting pretty close with the solution from here: Counting MAX number of consecutive blank cells

Below is a sample table of data to illustrate my example.

	1 Jan	2 Jan	3 Jan	4 Jan	5 Jan	6 Jan	7 Jan	8 Jan	Refund ($10/day)
Student A	0	1	0	0	0	0	1	1	$40
Student B	1	0	1	0	1	0	0	1	$0

I am trying to determine how much I need to refund the parents based on the attendance sheet exported from the student management software. 0 being absent, 1 being present

We will only refund if student is absent consecutively for 3 or more days ($10/day).
For eg, if student absent for 2 consecutive days = no refund, absent 3 consecutive days = $30, and so on

How should I write my formula in the refund column to do that? thanks!!!

 

[vladimiratanasiu]

Is it what you need?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan	12-Jan	13-Jan	Refund ($10/day)
2	Student A	0	1	0	0	0	0	1	1	1	0	0	0	0	80
3	Student B	1	0	1	1	0	0	1	1	0	0	1	1	1	0
4	Student C	1	1	0	0	0	0	0	1	1	1	0	0	1	50
Sheet1

Cell Formulas
Range	Formula
O2:O4	O2	=SUM(IFERROR(3+SQRT(FREQUENCY(IF(B2:N2=0,COLUMN(B2:N2)),IF(B2:N2<>0,COLUMN(B2:N2)))-3)^2,0))*10
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1		01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	Refund ($10/day)
2	Student A	0	1	0	0	0	0	1	1	40
3	Student B	1	0	1	0	1	0	0	1	0
Master

Cell Formulas
Range	Formula
J2:J3	J2	=LET(d,B2:I2,s,SEQUENCE(,COLUMNS(d)),f,FREQUENCY(IF(d=0,s),IF(d<>0,s)),SUM(FILTER(f,f>=3,0))*10)