How to count the five consecutive 'x' value within the 10 horizontal cells

[luck99]

Hello everyone,

I need assistance with an Excel problem.

I would like to count the occurrences of 5 consecutive 'x' values within a horizontal range of 10 cells, such as from A1 to J1.

Your help with this Excel issue would be greatly appreciated.

Thank you.

 

[hagia_sofia]

Hello, what version of Excel are you using + what is the desired output (e.g. whether or not there are 5 consecutive occurrences within a range or how many of them are there)?

 

[luck99]

Good day, hagia_sofia,

I'm working on a student attendance spreadsheet and need help with a specific calculation. I want to count how many students have 5 consecutive absences, regardless of the dates they were absent. For example, if a student has 5 consecutive days marked as "home visitation", and counts as one instance, even if the absences occurred across different weeks.

Could you please help me with this calculation? Any guidance you can provide would be greatly appreciated.

Thank you.

 

[luck99]

Regarding to the first question I'm using the version 2016. And there are 25 days/cells.
Thanks you

 

[hagia_sofia]

Thank you for your answers! Could you also please post a sample of the data so that we could see what counts as absence and how the data is structured?

 

[Sanjeev1976]

You can try :

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
1	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		Count
2	-	x	x	x	x	-	x	x	x	x	x	x	x	-	x	x	-	x	x	-	x	-	-	x	-		7
3	x	-	-	x	x	x	x	x	-	-	x	-	x	x	x	-	x	x	x	x	-	x	-	-	-		5
4	x	-	x	x	-	x	-	-	-	x	x	-	-	-	x	x	x	x	-	-	-	-	x	-	-
5	x	x	-	-	x	-	-	x	x	x	x	x	x	-	x	x	-	x	x	-	-	-	-	x	x		6
6	-	-	-	-	-	x	-	x	-	x	x	x	x	-	x	x	x	x	x	-	-	x	x	-	x		5
7	-	-	-	-	x	-	x	x	-	x	-	-	x	x	-	-	-	-	-	x	-	x	-	x	x
8	x	x	x	x	-	-	x	x	-	-	x	x	x	x	x	x	-	x	-	-	x	-	x	-	x		6
9	x	-	-	x	x	-	x	x	x	-	-	-	-	-	-	x	-	x	x	x	-	x	x	-	x
10	x	x	x	-	-	x	x	-	x	x	-	-	-	x	x	-	x	x	x	-	-	-	-	x	x
11	-	-	-	-	-	x	-	x	x	-	-	-	x	-	-	x	-	-	-	-	-	x	-	x	-
12	-	x	x	x	x	-	x	-	x	x	x	x	x	-	x	x	-	x	x	-	x	-	-	x	-		5
Sheet1

Cell Formulas
Range	Formula
AA2:AA12	AA2	=LET(a,LEN(SUBSTITUTE(TEXTSPLIT(TEXTJOIN(", ",0,A2:Y2),"-"),", ","")),TEXTJOIN(", ",1,IF(a>=5,a,"")))

 

[hagia_sofia]

Hello @Sanjeev1976, the solution needs to be for Excel 2016 - cf. post #4.

 

[luck99]

Thanks a bunch Sanjeev1976
This is really helpful

 

[luck99]

I'll try the solution. If not working then I'll let you know. Thanks foe your help..

 

[Peter_SSs]

Welcome to the MrExcel board!

Regarding to the first question I'm using the version 2016.

Why not put that information in your profile? Makes it very hard for helpers to provide good suggestions if they do not know what resources you have available in your Excel version.