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

alm395

New Member
Joined
Apr 23, 2018
Messages
21
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
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name12/28/202012/29/202012/30/202012/31/20201/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeDay OffDay OffOut Of OfficeActiveOut Of OfficeOut Of OfficeActive5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0
Sheet1
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,767
Test this out and see if it works for you:

Book1
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name####################################1/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4132
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4444
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4222
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9999
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0000
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeDay OffDay OffOut Of OfficeActiveOut Of OfficeOut Of OfficeActive5121.5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0000
Sheet3
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
O3:O11O3=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:P11P3=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:Q11Q3=AVERAGE(O3:P3)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

alm395

New Member
Joined
Apr 23, 2018
Messages
21
Test this out and see if it works for you:

Book1
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name####################################1/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4132
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4444
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4222
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9999
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0000
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeDay OffDay OffOut Of OfficeActiveOut Of OfficeOut Of OfficeActive5121.5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0000
Sheet3
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
O3:O11O3=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:P11P3=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:Q11Q3=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,767
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

New Member
Joined
Apr 23, 2018
Messages
21

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,767
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
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name####################################1/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4132
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4444
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4222
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9454.5
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0000
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeOut of OfficeOut of OfficeOut Of OfficeActiveOut Of OfficeOut Of OfficeActive7142.5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0000
Sheet3
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
O3:O11O3=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11P3=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11Q3=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.
 
Solution

alm395

New Member
Joined
Apr 23, 2018
Messages
21
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
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name####################################1/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4132
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4444
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4222
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9454.5
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0000
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeOut of OfficeOut of OfficeOut Of OfficeActiveOut Of OfficeOut Of OfficeActive7142.5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0000
Sheet3
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
O3:O11O3=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11P3=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11Q3=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
ABCDEFGHIJKLMNOPQ
1Out of Office
2Full Name####################################1/1/20211/2/20211/3/20211/4/20211/5/20211/6/20211/7/20211/8/2021TOTAL OFFMIN OFFMAX OFFAVG OFF
3PERSON 1ActiveOut Of OfficeActiveActive09UDay OffDay OffActiveActiveOut Of OfficeOut Of OfficeOut Of Office4132
4PERSON 213SActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
5PERSON 3ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeActiveActiveActiveActiveActive4444
6PERSON 4ActiveActiveActiveActiveActiveDay OffDay OffActiveActiveActiveActiveActive0000
7PERSON 5Out Of OfficeOut Of OfficeActiveActiveActiveDay OffDay OffActiveActiveActiveOut Of OfficeOut Of Office4222
8PERSON 6ActiveOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of OfficeOut Of Office9454.5
9PERSON 7Active13SActive13S13SDay OffDay OffActiveActiveActiveActiveActive0000
10PERSON 813S09UOut Of OfficeActiveOut Of OfficeOut of OfficeOut of OfficeOut Of OfficeActiveOut Of OfficeOut Of OfficeActive7142.5
11PERSON 913S13S13SActiveActiveDay OffDay OffActiveActiveWFHWFHWFH0000
Sheet3
Cell Formulas
RangeFormula
N3:N11N3=COUNTIF(B3:M3,$N$1)
O3:O11O3=MIN(IFERROR(1/(1/FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3)))),""))
P3:P11P3=MAX(FREQUENCY(IF(B3:M3="out of office",COLUMN(B3:M3)),IF((B3:M3<>"out of office"),COLUMN(B3:M3))))
Q3:Q11Q3=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! 🍻
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,228
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top