Counting Blank Rows

[nigelk]

Hi all,

I have a problem that I'm finding very difficult to solve.

I'm importing data from a website into column A on a 1 min timer.
Sometimes a row will empty, sometimes not, and I can't find a way to count the empty rows between A1 and the last non-empty cell.

The Countblank function, for example, requires a specific range, but as I don't know where the end of the range is I can't use it.

Is there a formula/function that I could use that just looks at col A and returns the last non-blank cell, whether it's in row 2 or 200?

Thank,Nigel.

 

[nigelk]

If I have 200 in A14 and 150 in A6, it's returning a value of 10?, but it should return 6, which is row 6.

Thanks, Nigel.

 

[nigelk]

Biff,

I changed it back, but with 2 entries, (200 in A14 and 150 in A6), it's returning a value of 11, not 6

The formula is in A1, and has been entered as an array. Can I ask what result you got?

Thanks,Nigel

 

[Haseeb Avarakkan]

Nigel,

The above provided formulas are COUNTING the blank cell between the start cell to the last_non_blank_cell in A:A, let's say A2:Last_non_blank_cell

If you have 200 in A14 & 150 in A6, the range is A2:A14 (A14 is the last_non_blank_cell). So COUNTING blank cell between A2:A14, which is 11.

How are you saying it should be 6? Do you want to get the First_non_blank_cell in A:A, Which is 6?

 

[nigelk]

Ah! light at the end of the tunnel.

Thank you very much!

Nigel.

 

[rhivert]

Finding the position of first non-blank cell in a column or list

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 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.

I tried to tweak the sum if formula presented in this thread but no luck.

Also, I do not want to use any VBA because It is dynamic ranges for 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]

Hello,

Based on your data, let's say A:B. If your clinic_type is in B, may be a COUNTIF would do it,

=MAX(0,COUNTIF(B:B,C2&"*")-1)

C2 = Ottawa

Or,

=COUNTBLANK(INDEX(A:A,MATCH(C2,A:A,0)):INDEX(A:A,MATCH(C2,A:A,0)+COUNTIF(B:B,C2&"*")-1))

 

[T. Valko]

Finding the position of first non-blank cell in a column or list

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 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.

I tried to tweak the sum if formula presented in this thread but no luck.

Also, I do not want to use any VBA because It is dynamic ranges for 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

If that is a true representation of your data then this will do it.

Book1
	A	B	C	D
2	Ottawa	Ottawa Permanent		Ottawa
3		Ottawa Mobiles		3
4		Ottawa Bloodmobiles
5	Kingston	Kingston Permanent
6		Kingston Mobiles
7	Sudbury	Sudbury Permanent
8		Sudbury Mobiles
Sheet1

Formula entered in D3:

=COUNTIF(B:B,D2&"*")