Count Consecutive Text in Rows (MIN, MAX, AVG)

[alm395]

I have a table with employees down column A and dates going across (3/20/20 - 4/4/21). Each date is tracked with that person's daily status. I already have a section that counts the totals of each status type, but am now looking to find the min, max, and avg of consecutive dates with the specific status of "Out Of Office." Our schedule also includes weekends, so their status may or may not be filled in during those two days.

Example:
PERSON 1: MIN=1 / MAX=3 / AVG=2
PERSON 3: MIN=4 / MAX=4 / AVG=4
PERSON 6: Even though the weekends are left blank, I would like the outcome for consecutive days to be 9.
PERSON 8: MIN=1 / MAX=2 / AVG=1.67

Please help! I have about 1100 employees we are trying to gather this information on before the end of our fiscal year.

Thank you in advance!!!

Min Max Avg Days.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	12/28/2020	12/29/2020	12/30/2020	12/31/2020	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Day Off	Day Off	Out Of Office	Active	Out Of Office	Out Of Office	Active	5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0
Sheet1

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)

 

[Eric W]

Test this out and see if it works for you:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	#########	#########	#########	#########	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4	1	3	2
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4	4	4	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4	2	2	2
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9	9	9	9
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Day Off	Day Off	Out Of Office	Active	Out Of Office	Out Of Office	Active	5	1	2	1.5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)
O3:O11	O3	=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office")*(WEEKDAY(B$2:M$2,2)<6),COLUMN(B3:M3)))),""))
P3:P11	P3	=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office")*(WEEKDAY(B$2:M$2,2)<6),COLUMN(B3:M3))))
Q3:Q11	Q3	=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alm395]

Test this out and see if it works for you:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	#########	#########	#########	#########	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4	1	3	2
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4	4	4	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4	2	2	2
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9	9	9	9
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Day Off	Day Off	Out Of Office	Active	Out Of Office	Out Of Office	Active	5	1	2	1.5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)
O3:O11	O3	=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office")*(WEEKDAY(B$2:M$2,2)<6),COLUMN(B3:M3)))),""))
P3:P11	P3	=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office")*(WEEKDAY(B$2:M$2,2)<6),COLUMN(B3:M3))))
Q3:Q11	Q3	=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.

So my boss literally just called me after I’ve been trying to figure this out all day & changed it up on me. ??‍

Now, for PERSON 6 & 8 where they had “Out of Office” on Friday & Monday, I am to now fill in Saturday & Sunday with “Out of Office” to get the consecutive count.

This is getting complicated now.

 

[Eric W]

As far as I can see, it should make no difference in the formulas if you have anything in the Saturday/Sunday columns. Or do you mean that Person 6 should have a max count of 11, and Person 8 have a max count of 4?

 

[alm395]

As far as I can see, it should make no difference in the formulas if you have anything in the Saturday/Sunday columns. Or do you mean that Person 6 should have a max count of 11, and Person 8 have a max count of 4?

Yes, Person 6 would have a max count of 11, and Person 8 would have 4.

I am going to update the report on my end to show "Out Of Office" on those weekends that show Friday and Monday with the same status.

 

[Eric W]

If you actually put "Out of Office" in Saturday and Sunday when Friday and Monday also have "Out of Office", then you can use the existing formula and it will work. But if you do that, you can also use a slightly shorter formula if you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	#########	#########	#########	#########	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4	1	3	2
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4	4	4	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4	2	2	2
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9	4	5	4.5
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Out of Office	Out of Office	Out Of Office	Active	Out Of Office	Out Of Office	Active	7	1	4	2.5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)
O3:O11	O3	=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11	P3	=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11	Q3	=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.

It changes the behavior a bit, you can see the difference on Person 6 and Person 8.

 

[alm395]

If you actually put "Out of Office" in Saturday and Sunday when Friday and Monday also have "Out of Office", then you can use the existing formula and it will work. But if you do that, you can also use a slightly shorter formula if you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	#########	#########	#########	#########	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4	1	3	2
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4	4	4	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4	2	2	2
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9	4	5	4.5
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Out of Office	Out of Office	Out Of Office	Active	Out Of Office	Out Of Office	Active	7	1	4	2.5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)
O3:O11	O3	=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11	P3	=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11	Q3	=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.

It changes the behavior a bit, you can see the difference on Person 6 and Person 8.

If you actually put "Out of Office" in Saturday and Sunday when Friday and Monday also have "Out of Office", then you can use the existing formula and it will work. But if you do that, you can also use a slightly shorter formula if you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Out of Office
2	Full Name	#########	#########	#########	#########	1/1/2021	1/2/2021	1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	TOTAL OFF	MIN OFF	MAX OFF	AVG OFF
3	PERSON 1	Active	Out Of Office	Active	Active	09U	Day Off	Day Off	Active	Active	Out Of Office	Out Of Office	Out Of Office	4	1	3	2
4	PERSON 2	13S	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
5	PERSON 3	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Active	Active	Active	Active	Active	4	4	4	4
6	PERSON 4	Active	Active	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
7	PERSON 5	Out Of Office	Out Of Office	Active	Active	Active	Day Off	Day Off	Active	Active	Active	Out Of Office	Out Of Office	4	2	2	2
8	PERSON 6	Active	Out Of Office	Out Of Office	Out Of Office	Out Of Office			Out Of Office	Out Of Office	Out Of Office	Out Of Office	Out Of Office	9	4	5	4.5
9	PERSON 7	Active	13S	Active	13S	13S	Day Off	Day Off	Active	Active	Active	Active	Active	0	0	0	0
10	PERSON 8	13S	09U	Out Of Office	Active	Out Of Office	Out of Office	Out of Office	Out Of Office	Active	Out Of Office	Out Of Office	Active	7	1	4	2.5
11	PERSON 9	13S	13S	13S	Active	Active	Day Off	Day Off	Active	Active	WFH	WFH	WFH	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
N3:N11	N3	=COUNTIF(B3:M3,$N$1)
O3:O11	O3	=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11	P3	=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11	Q3	=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.

It changes the behavior a bit, you can see the difference on Person 6 and Person 8.

This worked perfectly! Thank you soo much for taking time to help me! ?

 

[Eric W]

Happy to help! Thanks for the update!