count between two cells

February 8, 2009 at 09:10:40
Specs: Windows XP
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.

See More: count between two cells

Report •

February 8, 2009 at 15:47:14
I don't think you'll find a built-in function within Excel to do what you want. There is no COUNT function that I know where you can set the types of parameters you require: Count the occurances of a specific value but only if each single or contiguous occurance of that value it is bracketed by another specific value, which by the way, needs to be counted also.

However, Excel allows for the creation of User Defined Functions (UDF) which can be used just like built-in functions, except that the user writes some VBA code that runs each time the function is evaluated.

If my testing was accurate, the following UDF seems to do what you want. It will count each A but it will also check to see if any O's follow the A and count them also. However, once it runs out of contiguous O's to count, it checks to see if the next value is an A. If it is an A, it adds the O count to the A count. If not, it resets the O count to zero until it finds another A and starts the process again.

Store the function code in a regular VBA module in your workbook and enter


in any cell. You can change the range you are using the function on, but it is written to count only the values in the first column of the range entered.

Function CountMyA(ByRef MyRange As Range)
MyCell = 1
 If MyRange.Cells(MyCell, 1) = "A" Then A_Count = A_Count + 1
 If MyRange.Cells(MyCell, 1) = "A" And _
    MyRange.Cells(MyCell, 1).Offset(1, 0) = "O" Then
  NxtCell = 1
  ChkCell = MyRange.Cells(MyCell, 1)
   Do Until ChkCell <> "A" And ChkCell <> "O"
    If NxtCell = MyRange.Rows.Count Then GoTo Function_Done
      O_Count = O_Count + 1
       If MyRange.Cells(MyCell, 1).Offset(NxtCell, 0) = "A" Then
        A_Count = A_Count + O_Count - 1
        Exit Do
       End If
    NxtCell = NxtCell + 1
    ChkCell = MyRange.Cells(MyCell, 1).Offset(NxtCell, 0)
  MyCell = MyCell + NxtCell
  O_Count = 0
  GoTo NextMyCell
 End If
  If MyCell > MyRange.Rows.Count Then
   CountMyA = A_Count
   Exit Function
  End If
 MyCell = MyCell + 1
 GoTo NextMyCell
End Function

Report •

February 8, 2009 at 20:23:17
Thank you. It worked. May I request you to tell me if instead of A1:A31, the data is in A1:AD1.

Please Help.

Report •

February 9, 2009 at 07:04:26
Well, A1:AD1 changes things.

Since your original requirement was checking a column of values, and now you are asking to check a row of values, the code needs to altered.

Before I do any more work, tell me this:

Does the function need to work for both columns and rows or just rows. Making it work for one or the other is fairly easy, but making it work for both means some more complicated coding. The code would need to first determine whether a column or row was being evaluated before doing it's job.

Report •

Related Solutions

February 9, 2009 at 20:00:00
It is only for row of values. My apologies for miscommunication.

Report •

Ask Question