how to use CountIf with Offset for values in Range, last n rows

Hi analystbank,

I ain't sure I got exactly what you asked for.
But if you want to limit yourself to a certain amount of data, you could try :

Please Login or Register  to view this content.

That would produce 12, the same way would

Please Login or Register  to view this content.

then you were on the right track with

Please Login or Register  to view this content.

It would give you the last value. To return the last row number, you just need to modify it slightly into :

Please Login or Register  to view this content.

I subtracted the 15 first rows you aren't counting apparently. Adjust to your likings.

If it's not quite what you were looking for, please bear with me.
Also why save in .xlsm, I didn't see any macro in there?

Thanks for feedback, I am checking, how to combine formulas to get result you have given in Cell C6.

In my ever expanding work, i would not know, if it always starts with B15, that is why I have added a controller in Cell A7 which should count back from last cell in Column B and move n rows up and consider it as range and do the arithmatic. I guess, some loopholes in above.

Sorry, but it wont be flexible to my requirement.

File is in xlsm becuase it has Macros in my main workbook.

Hi.

Are you saying you want to define how many values are below the last value entered in column B in the range between last value minus 1 and last value minus n ?
And to set n you simply enter it in A7?

I was under the impression there was a calculation need to get to this n.

If so you could then try a variant in C6 :

Please Login or Register  to view this content.

where this part gives you the last row number (35)

Please Login or Register  to view this content.

So until I understand how you get the 20, you can just refer to your A7 cell instead of mu calculated B7 one.

Cheers

non volatile
=countif(index(b:b;lookup(2;1/(b12:b5000<>0);row(b12:b5000))-1):index(b:b;max(12;lookup(2;1/(b12:b5000<>0);row(b12:b5000))-A7-1));"<"&b4)

Please try

=COUNTIFS(INDEX(B12:B5000,MATCH(9^9,B12:B5000)-A7):B5000,"<"&B4)

Formula:

Please Login or Register  to view this content.

Many thanks to all

@BMV
Excel 2010 find error in formula and suggest to use Insert Function to fix problem

@Bo_Ry
suggested solution, works well. I am moving on with my work , keep this suggestion, as it also has alternate way to find MIN, MAX etc and appears to be flexible to my work requirements.

@Shareez
suggested solution, works well, I just changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

not sure, what -21 does and should i link to some cell or leave as it is.

My Controller in A7 may change to modify/change the range criteria.

Try this:

=sumproduct(--(offset($b$12,counta(b12:b5000)-(A7+1),,A7)<b4))