# Count cells in a column between values until specific value reached in Excel

I am trying to count cells from a column between two values: I have a list which on the range A2:A118. I want to start counting when a specific value is reached and stop when another specific value is reached. Let's say I want to start counting when in range A2:A118, A4 = "Apples", it'd start counting A5 = ["another fruit"], A6 =["another fruit"], A7= ["another fruit"] [...] until in A30 it reaches "Bananas" then it would stop counting. I'm not sure if that achievable with just formulas or if I would have to use VBA for making a loop or something.

The MATCH function returns row numbers. Simple subtraction will produce the result.

``````=MATCH("Bananas", A:A, 0)-MATCH("Apples", A:A, 0)
``````

You may or may not want to add `-1` depending on whether you want to include the Apples row or not.

• In OP case (it seems) it needs: find 1st border by MATCH, fing 2nd border by another MATCH starting from 1st border, then use COUNTIF in the range of previously found borders for to obtain the result... – Akina Sep 19 '19 at 7:58

Simple `COUNTIF` formula solves the issue: Formula in Cell `L100:`

`=COUNTIF(K\$100:K100,K100)`

Adjust cell references in the formula as needed. ``````D4=COUNTIF(INDIRECT("A"&MATCH(D1,A1:A20,0)&":A"&(MATCH(D1,A1:A20,0)+MATCH(D2,INDIRECT("A"&MATCH(D1,A1:A20,0)&":A20"),0)-1)),D3)
``````