# Number of Child ID has >= 3 Consecutive Days Analysis in Excel

#### Lorens29

##### New Member
Hello,

Please help me find the Excel Formula to count number of Child ID has FALSE >=3 consecutive days.
I hope that someone can help me with the problem. I have attached an excel with the sample data.
Thank you to anyone who can solve this for me, and provide me with the excel logic to this, - please updated the table to show how
Many thanks once again. I hope my explanation was clear. I have googled and tried - but with little or no success.

#### Attachments

• 10.4 KB Views: 4

#### GraH - Guido

##### Well-Known Member
Try this to indicate on which date the period of 3 or more days begins
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&([@[Attendance Date]]+2),[Attendance Date],">="&[@[Attendance Date]])>=3

Or when it ends
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&([@[Attendance Date]]-2),[Attendance Date],"<="&[@[Attendance Date]])>=3

#### Attachments

• 11.6 KB Views: 3

#### Lorens29

##### New Member
Thank you bro.

This formula work well.
If you don't mind, can you help me again to combine the formulas:
1. =COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&([@[Attendance Date]]+2),[Attendance Date],">="&[@[Attendance Date]])>=3 and =COUNTIF(Table1[Consecutive Absences of 3 or more days Start],TRUE())
2. =COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&([@[Attendance Date]]-2),[Attendance Date],"<="&[@[Attendance Date]])>=3 and =COUNTIF(Table1[Consecutive Absences of 3 or more days End],TRUE())
The combine formula will help me to answer my problem that the number of Child ID who has >=3 Consecutyive absences equal 2 Child ID.

Thank you for help

#### Lorens29

##### New Member
Yes, I was posted it.
Can you help me Alan?

#### GraH - Guido

##### Well-Known Member
Dear Lorens29,

You might want to visit the forum rules. And understand why it is polite to indicate in your post you have crossposted.

As for the formula:
- I noticed there could be a problem related to Sundays, as there is no school that day (from your data).
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&WORKDAY.INTL([@[Attendance Date]],2,11),[Attendance Date],">="&[@[Attendance Date]])>=3
= COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&WORKDAY.INTL([@[Attendance Date]],-2,11),[Attendance Date],"<="&[@[Attendance Date]])>=3

I don't understand your latest question. Do you want to indicate all involved rows of the periods per child?

#### Lorens29

##### New Member
Dear Lorens29,

You might want to visit the forum rules. And understand why it is polite to indicate in your post you have crossposted.

As for the formula:
- I noticed there could be a problem related to Sundays, as there is no school that day (from your data).
=COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],"<="&WORKDAY.INTL([@[Attendance Date]],2,11),[Attendance Date],">="&[@[Attendance Date]])>=3
= COUNTIFS([Child ID],[@[Child ID]],[Child is Present?],FALSE(),[Attendance Date],">="&WORKDAY.INTL([@[Attendance Date]],-2,11),[Attendance Date],"<="&[@[Attendance Date]])>=3

I don't understand your latest question. Do you want to indicate all involved rows of the periods per child?
Hi GraH - Guido,

Thank you for reminding me. I will deleted my posting on excelguru.
Thanks for noticed.
My latest question Is there perhaps a direct formula for getting results that there are 2 ChildIDs who were absent for 3 days or more?
Please look my explain in attachment (E1:F2) like this picture.

The first Formula is correct because sundays and holidays not included in active day.

Thanks

#### Attachments

• 12.4 KB Views: 1
Last edited:

#### AlanSidman

##### Well-Known Member
I think that you may have missed the point of notifying when crossposting. It is not necessary to delete one or the other if you have crossposted. It is all about notifying each forum that you have crossposted and where. In this manner, anyone willing to help can see if there has been a solved response or not and decide whether to help or not. Reread the link I offered in post #6. This is a standard request at nearly all forums.

#### Lorens29

##### New Member
Thank you for notify me.

#### Lorens29

##### New Member
Hi @GraH - Guido, \

I have update the excel file. Please look for help me to:
1. COUNT Number of Child ID Consecutive absent of 3 or more days in Feb
2. COUNT Number of Child ID absent of 3 or more days in Feb
Thank you

#### Attachments

• 16.8 KB Views: 2