Count no. rows between 2 values

[amoxuxu]

Hi all,

I need help creating a formula to count the number of rows in a column, as defined by parameters set in 2 other columns.

Simplified example
In columns A and B, I have the following:

Particle Frame
1 0
1 1
1 3
1 4
1 5
1 7
1 8
1 9
1 10
1 11
2 5
2 6
2 7
2 8
2 9
2 11
2 12
2 13
2 14
2 15
2 16

In separate columns I have:

Particle Start Frame End Frame
1 1 7
2 8 15

I would like to count the number of frames in column B as defined by the start and end frames above, and have this in a separate column.

Note that the frame count in column B is not always consecutive and some values are 'missed out' every now and then...

Please see example spreadsheet for clarity.

Thank you for your help!

[modytrane]

enter this formula in G2 and drag down.

[davsth]

assuming the values exist the following works as an array (enter with shft control enter)
=MATCH(1,($A$2:$A$22=$D2)*($B$2:$B$22=F2),0)-MATCH(1,($A$2:$A$22=$D2)*($B$2:$B$22=E2),0)+1

may be an option otherwise if the format you are using is conistent
=SUMPRODUCT(($A$2:$A$22=D2)*($B$2:$B$22>=E2)*($B$2:$B$22<=F2))

[modytrane]

one other option:

In G2: