Count consecutive more than 3 days absent in a month/year exclude Sat/Sun/PubHol

[suchetherrah]

Hi,

Would like to seek your assistance

1) In Entry sheet: Teams absent="N": count number of >=3 consecutive days in a year exclude Sat/Sun/PubHol in Column
2) In AttDashboard sheet: Teams absent="N": count number of >=3 consecutive days in a month exclude Sat/Sun/PubHol by Rows

Attached is excel sheet for your reference. Can anyone please advise how to go about this.

[samba_ravi]

Try below array formula
=SUM(IF(FREQUENCY(IF(Table1[Team1]="N",ROW(Table1[Team1])),IF(Table1[Team1]="Y",ROW(Table1[Team1])))>=3,1))

[suchetherrah]

Hi,

The formula is counting as one if there is one consecutive of 3 days. It should start the count after "Y" then check >=3 consecutive days until today's date.
User prefer to have blanks instead of typing "N" but it should stop the count if it's today's date.

I have highlighted in yellow the result to show as attached.

[suchetherrah]

Hi,

I have simplify the request, I only need a number of times count of >= 3 consecutive days after "Y"

AttDashboard sheet: Teams did not attend (" "): count number times of >=3 consecutive days until today in a month
Occurances starts after "Y" until today's data.

I was looking at all the samples but still didn't get an idea on the count. Attached is the worksheet for your preview.

Would really appreciate your advise. It will be a great help. Thank you.

[AliGW]

Duplicate thread closed. Continue here: https://www.excelforum.com/excel-for...ml#post4640524

Do NOT open duplicate threads on the same issue.