countif greater than X from the last 25 non blank data entries

[ben10nis]

Hi,

I'm looking to find the number of values above a certain number from the last 25 data points which are non blank. Usually i would use the COUNTIF function to find those greater than a value. I'm using the below formula to create the average of the last 25 non blank cells for a different purpose in the sheet. I now need the count/number of the 25 entries above a designated value {=AVERAGE(IF(ROW(I38:I20006)>=LARGE(IF(I38:I20006<>"",ROW(I38:I20006)),MIN(COUNT(I38:I20006),25)),IF(I38:I20006<>"",I38:I20006)))}
The problem i'm having is replacing the average with COUNTIF and entering the criteria as below or another variation of fails. Im quite new to excel and couldn't come up with any alternative formulas to create the range for the countif function
=COUNTIF(IF(ROW(I38:I20006)>=LARGE(IF(I38:I20006<>"",ROW(I38:I20006)),MIN(COUNT(I38:I20006),25)),IF(I38:I20006<>"",I38:I20006)),>I29))

Thanks

Ben

[samba_ravi]

=countif(index(i:i,max(large(index(row(i38:i20006)*(i38:i20006<>""),0),25),min(row(i38:i20006)))):index(i:i,max(index(row(i38:i20006),0))),">"&i29)

Please Login or Register  to view this content.

[ben10nis]

thanks very much Siva