0

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 2

1

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.

1
  • 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, 2019 at 7:58
0

enter image description here

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .