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:

enter image description here

Formula in Cell L100:


Adjust cell references in the formula as needed.


enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.