Hi, I need help in creating a excel formula to count the total occurrences of a consecutive count of 10. Let's say if a student has attended school first 10 days then it's his first occurrence. On 11th day, he is absent and again starts from 12th to 22th day. Total occurrences of 10 days, is 2. So that's how I wanna know total occurrences of 10. If this student has attended school for 6 months then how many consecutiveoccurrences of 10 will be there. Need help on this.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Counting the multiple consecutive occurrences of a fix sumtotal
- Thread starter alan581
- Start date
JamesCanale
Well-known Member
- Joined
- Jan 13, 2021
- Messages
- 1,221
- Office Version
- 365
- Platform
- Windows
This will count the number of streaks greater than 9 school days in a row. If the kid comes in 25 days, that won't be counted as 2, just 1. You might want 20 days or 30 days to count as 2 or 3 though. I'm not sure.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C1:D1 | C1 | =ROWS(FILTER(LEN(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",FALSE,C3:C186)," ","-")," ",""),,"-")),LEN(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",FALSE,C3:C186)," ","-")," ",""),,"-"))>9)) |
| B4:B50 | B4 | =WORKDAY.INTL(B3,1,1,$A$3:$A$24) |
Upvote
0
Hello James, thanks for helping. It seems like I was not able to explain the situation. So its like rewarding team members if they attend any event or training if they meet the attendance for fix number of days. In given example, that fix day was 10. I have attached an image with the requirement. Please help in same format. Regardless of how many days attended, just want to know the occurrences of 10 days consecutively.
Attachments
Upvote
0
JamesCanale
Well-known Member
- Joined
- Jan 13, 2021
- Messages
- 1,221
- Office Version
- 365
- Platform
- Windows
Try:
| MrExcelPlayground17.xlsx | ||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | |||
| 1 | Name | Occurances | 5/1/2023 | 5/2/2023 | 5/3/2023 | 5/4/2023 | 5/5/2023 | 5/6/2023 | 5/7/2023 | 5/8/2023 | 5/9/2023 | 5/10/2023 | 5/11/2023 | 5/12/2023 | 5/13/2023 | 5/14/2023 | 5/15/2023 | 5/16/2023 | 5/17/2023 | 5/18/2023 | 5/19/2023 | 5/20/2023 | 5/21/2023 | 5/22/2023 | 5/23/2023 | 5/24/2023 | 5/25/2023 | 5/26/2023 | 5/27/2023 | 5/28/2023 | 5/29/2023 | 5/30/2023 | 5/31/2023 | 6/1/2023 | 6/2/2023 | 6/3/2023 | 6/4/2023 | 6/5/2023 | 6/6/2023 | 6/7/2023 | 6/8/2023 | 6/9/2023 | ||
| 2 | John | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
| 3 | Rose | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
| 4 | Sonia | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | ||||||||||||||||||
| 5 | Alex | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
| 6 | Nina | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||
Sheet11 | ||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B6 | B2 | =IFERROR(ROWS(FILTER(LEN(TEXTSPLIT(TEXTJOIN("",FALSE,C2:AP2),,"0")),LEN(TEXTSPLIT(TEXTJOIN("",FALSE,C2:AP2),,"0"))>9)),0) |
Upvote
1
Solution
