Change range based on value of a cell

[kwaldman]

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.

 

[Pete_UK]

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

 

[Roger Govier]

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")

 

[kwaldman]

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

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

 

[Pete_UK]

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

Pete