Count the 5 consecutive 'x' in excel 2016

[luck99]

Hello everyone,

I need assistance with an Excel problem.

I would like to count the occurrences of 5 consecutive 'x' values.

Your help with this Excel issue would be greatly appreciated.

Thank you.
Here is the sample format
Version of my excel is
Excel 2016

 

[jolivanes]

I have a solution but I don't know if it works on the "Prefer Not To Say" version.

 

[MARK858]

I have a solution but I don't know if it works on the "Prefer Not To Say" version.

Version of my excel is
Excel 2016

Plus in the title

 

[jolivanes]

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

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.

www.mrexcel.com

Mark.
That is my answer to a polite request that was made in Post #10 and totally ignored.

I am not a moderator or administrator so I can't say what I think should be said to try to make people be polite to helpers that spend their time to solve problems for people in need.

 

[Sanjeev1976]

You can try the below solution :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	sample only september
2	days	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F	ABSENT	5 CONSECUTIVE ABSENCES	Remarks
3	Emp 1			x	x	x	x	x									5	1	REPORT
4	Emp 2	x	x						x			x		x			5
5	Emp 3	x	x	x			x	x	x			x	x	x	x	x	11	1	REPORT
Sheet2

Cell Formulas
Range	Formula
Q3:Q5	Q3	=COUNTA(B3:P3)
R3:R5	R3	=IF(ISNUMBER(SEARCH("xxxxx", IF(B3=""," ",B3) & IF(C3=""," ",C3) & IF(D3=""," ",D3) & IF(E3=""," ",E3) & IF(F3=""," ",F3) & IF(G3=""," ",G3) & IF(H3=""," ",H3) & IF(I3=""," ",I3) & IF(J3=""," ",J3) & IF(K3=""," ",K3) & IF(L3=""," ",L3) & IF(M3=""," ",M3) & IF(N3=""," ",N3) & IF(O3=""," ",O3) & IF(P3=""," ",P3))), 1, " ")

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	sample only september
2	days	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F	ABSENT	5 CONSECUTIVE ABSENCES	Remarks
3	Emp 1			x	x	x	x	x									5	1	REPORT
4	Emp 2	x	x						x			x		x			5	0
5	Emp 3	x	x	x			x	x	x			x	x	x	x	x	11	1	REPORT
Sheet1

Cell Formulas
Range	Formula
Q3:Q5	Q3	=COUNTIF(B3:P3,"X")
R3:R5	R3	=SUMPRODUCT(--(FREQUENCY(COLUMN(B3:P3),IF(B3:P3<>"X",COLUMN(B3:P3)))>4))

 

[MARK858]

@luck99 what do you want returned for 10 consecutive days absent?

 

[Sanjeev1976]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	sample only september
2	days	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F	ABSENT	5 CONSECUTIVE ABSENCES	Remarks
3	Emp 1			x	x	x	x	x									5	1	REPORT
4	Emp 2	x	x						x			x		x			5	0
5	Emp 3	x	x	x			x	x	x			x	x	x	x	x	11	1	REPORT
Sheet1

Cell Formulas
Range	Formula
Q3:Q5	Q3	=COUNTIF(B3:P3,"X")
R3:R5	R3	=SUMPRODUCT(--(FREQUENCY(COLUMN(B3:P3),IF(B3:P3<>"X",COLUMN(B3:P3)))>4))

Hi Phuoc :

Your solution does not give the correct results :

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	days	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F	ABSENT	5 CONSECUTIVE ABSENCES
3	Emp 1			x	x	x	x			x							5	1
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=COUNTIF(B3:P3,"X")
R3	R3	=SUMPRODUCT(--(FREQUENCY(COLUMN(B3:P3),IF(B3:P3<>"X",COLUMN(B3:P3)))>4))

 

[MARK858]

Your solution does not give the correct results :

@Sanjeev1976 can you test the below please

=SUMPRODUCT(--(FREQUENCY(COLUMN($B$1:$P$1),IF($B2:$P2<>"X",COLUMN($B$1:$P$1))-1)>4))

Edited to match the cell references in Sanjeev's table in post 5

 

[Peter_SSs]

Hi Phuoc :

Your solution does not give the correct results :

I don't think yours does either does it? Wouldn't this be 2?
(Also, I think returning a space character " " as a null result is a generally a bad idea)

24 09 03.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F	ABSENT	5 CONSECUTIVE ABSENCES
3			x	x	x	x	x				x	x	x	x	x	10	1
5 Consecutive

Cell Formulas
Range	Formula
Q3	Q3	=COUNTA(B3:P3)
R3	R3	=IF(ISNUMBER(SEARCH("xxxxx", IF(B3=""," ",B3) & IF(C3=""," ",C3) & IF(D3=""," ",D3) & IF(E3=""," ",E3) & IF(F3=""," ",F3) & IF(G3=""," ",G3) & IF(H3=""," ",H3) & IF(I3=""," ",I3) & IF(J3=""," ",J3) & IF(K3=""," ",K3) & IF(L3=""," ",L3) & IF(M3=""," ",M3) & IF(N3=""," ",N3) & IF(O3=""," ",O3) & IF(P3=""," ",P3))), 1, " ")

@luck99 what do you want returned for 10 consecutive days absent?

@luck99
As well as Mark's question, what about even 6 consecutive?

For counting 5 consecutive, for you is the below example ..

• 0 because it is 6 consecutive not 5 consecutive, or
• 1 because it is one group of at least 5, or
• 2 because there is 5 consecutive from D3:H3 and there is 5 consecutive from E3:I3

(I'm also interested to see a response to my question in you other thread)

24 09 03.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	M	T	W	ТН	F	M	T	W	ТН	F	M	T	W	ТН	F
3			x	x	x	x	x	x
5 Consecutive