I have a attendance excel sheet of A1:A31 consisting of P,A,O,H as the entries(P-For Present, A – for Absent, O – for Weekly off, H – for Holiday).
Now, what I want is, if there is an ‘A’ before and after ‘O’, then I want the ‘O’ also to be counted as ‘A’ and give me a total no. of ‘A’.
For example:
if in A1:A30,
A5 is ‘A’
A6 is ‘O’
A7 is ‘O’
A8 is ‘A’
then when i use countif or any other count function, I want the result to be 4.
Kindly help. Thanks in advance.