count number of cells that meet certain criteria between two other cells with certain criteria

Question

I am looking for a way to count the number of cells meeting a specific criteria that are located between two other cells. For example:

  A                B      C       D      E
151867377   01:03.5 63500   1325    ARFC
151867611   01:03.5 63734   1325    BC25
151867611   01:03.5 63734   1325    M25T
151867613   01:03.5 63736   1325    ShowComms 1325 Track classified as HOSTILE. 
151868763   01:04.7 64886   1325    BC25
151868763   01:04.7 64886   1325    M25T
151868765   01:04.7 64888   1325    ShowComms 1325 Track classified as HOSTILE. 
151870850   01:06.8 66973   1325    AFOK
151870852   01:06.8 66975   1325    KR03
151872490   01:08.5 68613   1325    BC25

In column E, I am looking for the number of cells that start with B between the ARFC and the KR03 cells.

Answer 1

You could perhaps use something like this:

=COUNTIF(OFFSET(E2,MATCH(H2,E2:E11,0)-1,0,MATCH(H3,INDEX(E2:E11,MATCH(H2,E2:E11,0)):E11,0)),"B*")

I'm using OFFSET here to define the range in which you will count the cells.

The first MATCH indicates where the range starts, the second match indicates where it ends.

The second match uses INDEX and MATCH to make sure that the 'ending' cell is not before the 'starting' cell.

B* is for matching cells beginning with B.