[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sat Jun 16, 2012 11:07 am    Post subject: [SOLVED]count the cells between two specified numbers Hi, I need to know how to write a formula that counts the cells between two specified numbers in a Column, for example, In Column A, I´d like to count the cells between number 99 and 8, please look at the example below. A 99 5 6 2 3 1 8 The formula would give me the answer : 5 (as there are 5 cells between number 99 and 8 in Column A) thanks and have a great day!Last edited by fivefootnine on Sat Jun 16, 2012 11:14 pm; edited 1 time in total
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6733

 Posted: Sat Jun 16, 2012 11:19 am    Post subject: count() but you would have to know the cells not just the values_________________jrkrideau Kingston ON Canada Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sat Jun 16, 2012 11:47 am    Post subject: thanks for the fast reply, what I want to do is to count how many cells there is between A1(which may contain a frozen number, like 99) and a given number, let´s say 8. I would never know where the number 8 would appear,, it could appear in A2 (that would make the answer 0), or A100(that would make the answer 99) or anywhere between.. thanks
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia

Posted: Sat Jun 16, 2012 2:40 pm    Post subject:

If the numbers are in A1:A100 then try...
 Code: =MATCH(8;A1:A100;0)-MATCH(99;A1:A100;0)-1

Ken Johnson
_________________