refer to whole column

[Ken]

Hi!

How do I refer to a whole column by a number of a cell? For example, if a
number of a certain cell is 1, then the column will be A:A. If a number of a
cell is 3, the column will be C:C.

I will be using the result as the [Sum_Range] in a SUMIF function.

Thank you very much!

 

[Dave Peterson]

=sumif(x:x,"xxx",indirect("C"&z999,false))

Where x:x contains the value you want to compare.
And z999 contains the column number

Hi!

How do I refer to a whole column by a number of a cell? For example, if a
number of a certain cell is 1, then the column will be A:A. If a number of a
cell is 3, the column will be C:C.

I will be using the result as the [Sum_Range] in a SUMIF function.

Thank you very much!

 

[Jim Thomlinson]

You can do this without indirect. You can use index instead something like
this. The advantage is that index is not volatile saving the overhead of
recalculating.

=sumif(x:x,"xxx",INDEX(B:Z,,A1))

Where A1 contains the column number.

--
HTH...

Jim Thomlinson

=sumif(x:x,"xxx",indirect("C"&z999,false))

Where x:x contains the value you want to compare.
And z999 contains the column number

Hi!

How do I refer to a whole column by a number of a cell? For example, if a
number of a certain cell is 1, then the column will be A:A. If a number of a
cell is 3, the column will be C:C.

I will be using the result as the [Sum_Range] in a SUMIF function.

Thank you very much!