|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
NxtCell = NxtCell + 1
ChkCell = MyRange.Cells(MyCell, 1).Offset(NxtCell, 0)
MyCell = MyCell + NxtCell
O_Count = 0
If MyCell > MyRange.Rows.Count Then
CountMyA = A_Count
MyCell = MyCell + 1