Find number of empty rows between two cells in a column with given values

[rhivert]

Finding the position of first non-blank cell in a column or list between two cells in a column with given values

I hope someone could help me with the following issue:

I have a table in the following format:

Location.....Clinic Type

Ottawa......Ottawa Permanent
................Ottawa Mobiles
................Ottawa Bloodmobiles
Kingston.....Kingston Permanent
................Kingston Mobiles
Sudbury.....Sudbury Permanent
................Sudbury Mobiles

I am trying to create a dynamic range for the charts which are linked to the table above, which would find for example "Ottawa" and count the number of empty cells between "Ottawa and the following non-blank cell.. Which in this case would be Kingston.

This formula would then be inserted in an offset formula to create my dynamic range to pick the range of values corresponding the the respective value I am looking for in the table.

The problem is that the dimension of the table changes every week and the non-blank between two line items varies accordingly.

Also, I do not want to use any VBA because It is a dynamic ranges for a chart I am trying to come up with.

Basically to recap.. I need a formula that gives a count of the number of empty rows between two cells containing a certain value.....

Thanks in advance!

Randall

 

[Haseeb Avarakkan]

See this,

http://www.mrexcel.com/forum/showthread.php?t=547340&page=2

 

[rhivert]

Thanks guys...

My sheet would be more like this:

Company X of Canada Ltd.
April 25, 2011
Pivot Table & Chart Data Source Table

Column1..Column2..................Column3
Ottawa....Ottawa Permanent ....OT2500
............................................OT2501
..............Ottawa Mobiles.........OT2504
............................................OT2505
..............Ottawa Bloodmobile..OT2506
............................................OT2507
Kingston...Kingston Permanent..KI25500
.............................................KI25501
................Kingston Mobiles.....KI25502
Sudbury.....Sudbury Permanent...SU20500

I want the formula to scan through column 1... and find the number of rows between lets say ottawa and kingston..

Please advise.

Thank you,

Randall

 

[Haseeb Avarakkan]

Looks like the Col_3 starts with first 2 characters in Col_A. Ottawa >> OT, Kingston >> KT.... If it is the case, try

E2 = Ottawa

=COUNTIF(C:C,LEFT(E2,2)&"*")

 

[rhivert]

Unfortunately Ottawa and Kingston both star by the letter K ... is there a way around that (referring to another row which repeats itself)...

Appreciate your help in trying to resolve my issue!

 

[Haseeb Avarakkan]

Not a good one anyway try this;

E2 = Ottawa

=COUNTA(INDEX(C:C,MATCH(E2,A:A,0)):INDEX(C:C,MATCH(E2,A:A,0)+LOOKUP(1E+100,CHOOSE({1,2},1,MATCH(TRUE,ISTEXT(INDEX(A:A,MATCH(E2,A:A,0)+1):A1000),0)))))-(COUNTIF(B:B,E2&"*")>1)

 

[rhivert]

The forumla returns 1 for any valid values i put into cell E2 :S

 

[Haseeb Avarakkan]

I am sorry. Forgot to mention. This is an Array Formula, must hit CONTROL+SHIFT+ENTER, not just ENTER.

 

[rhivert]

Thats almost perfect Hasseb!! just need to tweek it to give me the position of the last empty cell.. Where should I put the minus 1 in the formula?

Thanks a bunch!!

 

[Aladin Akyurek]

Are you wanting to create a dynamic named range per city?