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

**Hyflex** · 05-10-2012, 10:50 AM

Hey guys,

This one should be really easy to fix but I can't remember how I did this before.

In B12 I have the number: 49231 which changes every few days

I have the following formula in my sheet:

Please Login or Register  to view this content.

I am wanting the countif to read the number 49231 from B12 instead of the 100000

So it acts something like:

Please Login or Register  to view this content.

I've tried all sorts of things like:

Please Login or Register  to view this content.

Thanks in advance
- Hyflex

**Fotis1991** · 05-10-2012, 11:02 AM

Hi

Take a look, here.

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

**dilipandey** · 05-10-2012, 11:09 AM

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>

**Fotis1991** · 05-10-2012, 11:12 AM

@dilipandey

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

**Hyflex** · 05-10-2012, 11:14 AM

Hey Fotis1991,

Thanks for your reply, unfortunately that isn't what I'm looking for I need it to look at the exact range instead of the entire column but the exact range changes each day however the value for the end row is located in B12

Thanks once again
- Hyflex

**dilipandey** · 05-10-2012, 11:15 AM

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

Regards,
DILIPandey

<click on below 'star' if this helps>

**Fotis1991** · 05-10-2012, 11:19 AM

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

**Hyflex** · 05-10-2012, 11:25 AM

Hey guys,

I've attached a simple example, please take a look.

Thanks for both your help so far, it's much appreciated.

**Fotis1991** · 05-10-2012, 11:29 AM

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?

**tigeravatar** · 05-10-2012, 11:32 AM

Hyflex,

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

Please Login or Register  to view this content.

**Hyflex** · 05-10-2012, 11:39 AM

Originally Posted by Fotis1991

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?

That looks like it should work but I can't get it to work in my example I've tried:

Please Login or Register  to view this content.

but it returns #REF

Originally Posted by tigeravatar

Hyflex,

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

Please Login or Register  to view this content.

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

Thanks guys for your help

**Marcol** · 05-10-2012, 11:41 AM

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

**tigeravatar** · 05-10-2012, 12:15 PM

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.

**Fotis1991** · 05-10-2012, 01:18 PM

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!

**tigeravatar** · 05-10-2012, 01:24 PM

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

**Fotis1991** · 05-10-2012, 02:22 PM

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

Pls, don't worry!

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

**Hyflex** · 05-11-2012, 06:51 AM

Thank you ever so much guys, your help is very much appreciated.

I've added to your reputations.

Thanks once again
- Hyflex

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions