Try this...

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
I have a long complex IF formula in which the following SUMPRODUCT formula
is
contained.

SUMPRODUCT(--(AE11:AE200))

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!