Change range based on value of a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kwaldman@gmail.com - 19 Mar 2008 10:01 GMT

Hi all,
I'm trying to do a COUNTIF, but want to change the range depending on
a number somewhere else in the spreadsheet. For example, if the number
is 3, I want to count from C1:G1, but if the number is 4 I want to
count from D1:G1.

I've used the address function to find out which cell I want to start
from, and then concatenated that to the end point of the range (this
never changes). So I'm left with a cell that contains the value of the
range (C1:G1 or D1:G1) depending upon the number which is input above.

Is it possible to use this cell, which contains the value C1:G1 to
replace the range in the COUNTIF function?

Thanks very much.

Reply to this Message

Pete_UK - 19 Mar 2008 10:19 GMT

Use it with the INDIRECT function, i.e.:

=COUNTIF(INDIRECT(your_cell),condition)

your_cell is the one containing the calculated range.

Hope ths helps.

Pete

On Mar 19, 9:01 am, kwald...@gmail.com wrote:

> Hi all,
> I'm trying to do a COUNTIF, but want to change the range depending on
[quoted text clipped - 11 lines]
>
> Thanks very much.

Reply to this Message

kwaldman@gmail.com - 19 Mar 2008 12:56 GMT

> Use it with the INDIRECT function, i.e.:
>
[quoted text clipped - 23 lines]
>
> > Thanks very much.

Pete,
That worked perfectly! Thank you so much, this saved my a lot of
searching.
Kate

Reply to this Message

Pete_UK - 19 Mar 2008 13:51 GMT

You're welcome, Kate - thanks for feeding back.

Pete

On Mar 19, 11:56 am, kwald...@gmail.com wrote:

> Pete,
> That worked perfectly! Thank you so much, this saved my a lot of
> searching.
> Kate

Reply to this Message

Roger Govier - 19 Mar 2008 11:47 GMT

Hi

You don't need to use an intermediate cell to concatenate the range, instead
use a cell just to hold the starting column number
Assuming your value of 3 or 4 is held in cell A1
=COUNTIF(INDEX($1:$1,$A$1):G1,"your_test")
Change the cell reference to suit

If you wanted to also vary the ending cell reference, then with that column
number held in A2
=COUNTIF(INDEX($1:$1,$A$1):INDEX($1:$1,$A$2),"your_test")

Signature

Regards
Roger Govier

> Hi all,
> I'm trying to do a COUNTIF, but want to change the range depending on
[quoted text clipped - 11 lines]
>
> Thanks very much.

Reply to this Message

Rate this thread: