Changing a CountIf Range so it read the end row from a cell

Hi

Take a look, here.

http://www.excelforum.com/excel-work...med-range.html

Hi Hyflex,

If you wanted to check if Range QJ18 : QJ100000 has B12 i.e., 49231, use the reference of B12 in place of QM17.... does this solves your query.?

But if you want your range i.e. QJ18 : QJ100000 to be dynamic where end of the range can be determined by B12, use Offset function to define the dynamic name.
Do little googling on this, else post a sample file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

@dilipandey

Better to use your example in the thread that i linked, in my previous post

Yes... Fotis.. Thanks for the heads-up...

Regards,
DILIPandey

<click on below 'star' if this helps>

So, it's better, as Dilip said, to upload a small sample workbook.

Or, give a try to this, first...

=COUNTIF($QJ$18:INDIRECT(B12),OM17))

In B12, you'll type the cell reference, like Q252, FOR EXAMPLE..


Is this, works for you?

Hyflex,

Using your example file, this formula in cell K2 should do the trick:

=COUNTIF($F$2:INDEX($F:$F,$B$12),H2)

Originally Posted by Hyflex

but won't hundreds of these start to create lag over sheets what expand over to JFD and down to 200k

Generally speaking, any time a worksheet is that large you're going to see Excel slow down. When you have that much data, I would recommend using a database application like Access or Oracle instead of Excel.

As far as the fomula itself, the formula Marcol and I proposed uses Index which is not a volatile function, and it doesn't actually look at the whole column, it just gets the position you designated in cell B12, so it is being used to only look at a specified range. I can't really think of a better way to do what you're asking. The only way to find out if it will be of use is to try it out on your actual workbook.

See this workbook, best to avoid volatile functions such as INDIRECT()

As far as the fomula itself, the formula Marcol and I proposed uses Index which is not a volatile function, and it doesn't actually look at the whole column, it just gets the position you designated in cell B12, so it is being used to only look at a specified range. I can't really think of a better way to do what you're asking. The only way to find out if it will be of use is to try it out on your actual workbook.

Angree with Tiger!

@Fotis, I meant no offense, and I apologize if I accidentally insulted you

No, NO, my friend. No need to apologize for anything.

Pls, don't worry!


In this case, Marcol's way, is better..