Help with changing column reference in formula

[GoBucks]

I have a long complex IF formula in which the following SUMPRODUCT formula is
contained.

SUMPRODUCT(--(AE11:AE20>0))

Depending on the value of cell A1, I would like to dynamically change the
column reference in the formula. For example:

IF A1 = 1, THEN use the column reference AE in the formula
IF A1 = 2, THEN use the column reference AF in the formula

Is there a way to accomplish this by INDIRECT or another formula? Thanks!

 

[Tom Hutchins]

Try

=SUMPRODUCT(--(OFFSET(AE9:AE18,0,A1-1)>0))

Hope this helps,

Hutch

 

[Tom Hutchins]

Oops! Of course, I meant

=SUMPRODUCT(--(OFFSET(AE11:AE20,0,A1-1)>0))

Hutch

 

[GoBucks]

Thank you Tom!!!

Oops! Of course, I meant

=SUMPRODUCT(--(OFFSET(AE11:AE20,0,A1-1)>0))

Hutch

 

[T. Valko]

Try this...

=COUNTIF(INDEX(AE11:AF20,,A1),">0")