Countif within Variable row based on label

[Dgenty]

Hi all,

I need to count the number times certain text appears in a column between variable rows. Really struggling to find a way to do this and any help is appreciated.

So in the example below How can I count all instance of "3.5 Tonne" if it appears between the labels "Gas installer" and "level 1 specialist" which are actual in column I in my spreadsheet and the 3.5 tonne appears in column K. Again these rows may change in placement and number of everyday.


[TABLE="width: 677"]
<tbody>[TR]
[TD]CPW Run[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Route[/TD]
[TD]Operator ID[/TD]
[TD]Vehicle Type[/TD]
[TD]Hours[/TD]
[TD]Registration[/TD]
[TD]Operator[/TD]
[TD]Primary[/TD]
[TD]Mate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]7 Tonne Unmarked[/TD]
[TD="align: right"]08:12[/TD]
[TD]Test 1[/TD]
[TD]Company[/TD]
[TD]Test 1[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]7 Tonne Unmarked[/TD]
[TD="align: right"]09:22[/TD]
[TD]Test 2[/TD]
[TD]Company[/TD]
[TD]Test 2[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gas Installer[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Route[/TD]
[TD]Operator ID[/TD]
[TD]Vehicle Type[/TD]
[TD]Hours[/TD]
[TD]Registration[/TD]
[TD]Operator[/TD]
[TD]Primary[/TD]
[TD]Mate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test1[/TD]
[TD]Company[/TD]
[TD]Test1[/TD]
[TD]Agency Mate 30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test2[/TD]
[TD]Company[/TD]
[TD]Test2[/TD]
[TD]Agency Mate 27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test3[/TD]
[TD]Company[/TD]
[TD]Test3[/TD]
[TD]Agency Mate 25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test4[/TD]
[TD]Company[/TD]
[TD]Test4[/TD]
[TD]Agency Mate 29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test5[/TD]
[TD]Company[/TD]
[TD]Test5[/TD]
[TD]Agency Mate 22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.5 Tonne[/TD]
[TD="align: right"]09:15[/TD]
[TD]Test6[/TD]
[TD]Company[/TD]
[TD]Test6[/TD]
[TD]Agency Mate 24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Level 1 Specialist[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Will it always be between "Gas Installer" and "Level 1 Specialist" you want to search?
More importantly will you always know the start and end points ("range") you want to search between?

 

[Dgenty]

Yes it will always be between these 2 labels the search needs to go from. They will always be in the same columns but the number of rows and where the rows start and finish may differ day to day. its a difficult one.

 

[Special-K99]

Does "Gas Installer" and "Level 1" always appear ONCE in the column or could there be more occurrences of them in that column?

If they only occur once in the column use this

=MATCH("Level 1 Specialist",A1:A20,0)-MATCH("Gas installer",A1:A20,0)-3