Formula to find first blank row in range

[DeeEmmEss]

A monthly workbook contains a worksheet for each day with a section that contains a differing number of rows each day. I have an array formula which finds the first blank row in a range:- {=MIN(IF(R7:R50="",ROW(R7:R50)))} and wish to include it as part of several other formulas on same sheet. I need to include this as occassionally the range overlaps onto the next section and can include results from next section and fudge the figures: =COUNTIFS(T7:T50,"1st") & "/" &COUNTA(T7:T50) =SUM(COUNTIFS(T7:T50,{"2nd","3rd"})) & "/" &COUNTA(T7:T50) are two examples which work adequately but I wish to tidy them up. I have tried every combination - except the correct one - frustration level has risen and before the laptop gets binned your help will be greatly appreciated

 

[James006]

Hello,

Not sure to understand your problem ...

But probably the Indirect() function should help you out ...

HTH

 

[DeeEmmEss]

Yes, but that's what I've had trouble implementing. Could you suggest how it would fit in the formula, thanks.

 

[James006]

Hello,

With your array formula producing the first blank row located say in cell A1 ...

You could test =COUNTIFS(T7:Indirect("T"&A1),"1st") & "/" &COUNTA(T7:Indirect("T"&A1))

HTH

 

[DeeEmmEss]

Yes, that works. Thankyou for your help, it's much appreciated.

 

[James006]

You are welcome ...

 

[Peter_SSs]

If I have understood correctly, here is another way without needing the volatile function INDIRECT

=COUNTIF(T7:INDEX(T7:T50,A1-ROW(T7)+1),"1st")&"/"&COUNTA(T7:INDEX(T7:T50,A1-ROW(T7)+1))

I have highlighted the two "+1"s in the formula. If you are only checking up to the row before the first blank row in column R, then those "+1"s wouldn't be needed. I included them just so my formula matched the range in the previously suggested formulas