Excel 2007 : Counting Non Blank Cells within a dynamic range

Hi All,

My first thread in here.

I have a situation where I have to count the number of Non Blank cells within a range which keeps on changing as situation changes.

To be more specific,

On Column D, from row 18 to row 29, I have values, then on row 30 I have a blank cell.
Again on Column D, from row 31 to row 40, I have values and then on row 41, I have a blank cell. I need a formula to count the number of Non blank cells until the first blank cell is reached or a formula when the first blank cell is hit.

The below formula searches through the range and gives me the number of the cell at which I have the blank cell. I have a blank cell at row 30 and hence i get the answer 13 using the below formula.

=MATCH(TRUE,INDEX(ISBLANK(D18:D32),0,0),0)

and

=COUNTA(D18:D29).

Another question is, in the above two formulas, how do I make the range into a dynamic range or a range which changes with change in another cell? I have cell K2 which has the value D5 and K3 as D19. How can I call the cell values into the above formula so that the range in the above formulae automatically changes to the values in the cells K2 and K3?

In simple terms, if K2 shows D256 and K3 shows D264, the formula should go and search the range between D256 to D264.

Please help. Been banging my head on this for the past two days. I am using Excel 2007.

Thanks..

Try this,

K2=256
K3=264

=MATCH(TRUE,INDEX(ISBLANK(INDEX(D:D,K2):INDEX(D:D,K3)),0,0),0)

and

=COUNTA(INDEX(D:D,K2):INDEX(D:D,K3))

Regards

Originally Posted by sailepaty

Try this,

K2=256
K3=264

=MATCH(TRUE,INDEX(ISBLANK(INDEX(D:D,K2):INDEX(D:D,K3)),0,0),0)

and

=COUNTA(INDEX(D:D,K2):INDEX(D:D,K3))

Regards

Thanks Bro..Works Beautifully...Thanks a lot...