Count of successive 'N' in a row reverting pack to zero when 'Y' entered.

jessrw

New Member
Joined
Jan 30, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am working on attendance records - see below.

I have a column at the end that needs to count the successive 'N' attendance in a row (start counting at 1 for first recording of it) and then continue counting for each successive 'N' in that row. But then the counting needs to restart when a Y is entered in the row.

I can remove the Mode and Yearly Unattend columns if it makes the formula more practical.

Any suggestions for a formula?

1738286550419.png
 
I can remove the Mode and Yearly Unattend columns if it makes the formula more practical.
It is easier if all of the "Attend" are together. Try:
Book1
DEFGHIJKLMNOP
1
2
3YYNNNN001234
4YNNYNY012010
Sheet6
Cell Formulas
RangeFormula
K3:P4K3=SCAN(0,D3:I3,LAMBDA(a,b,IF(b="N",a+1,0)))
Dynamic array formulas.
 
Upvote 0
If you want to keep the other columns, here's one way:
Book1
DEFGHIJKLMNOPQ
2AttendModeAttendModeYearly UnattendAttendModeAttendMode
3YOnlineYOnline1NOnlineNOnline0012
4YPersonNPerson2NPersonNPerson0123
Sheet6
Cell Formulas
RangeFormula
N3:Q4N3=SCAN(0,FILTER(D3:L3,D$2:L$2="Attend"),LAMBDA(a,b,IF(b="N",a+1,0)))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

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
Back
Top