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.
2 Answers
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...– AkinaSep 19, 2019 at 7:58
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)