Hi, I am trying to change the Sum Range B:B dynamically, since the Sum Range on each worksheet (REGION) starts from column B to Z. So I am expecting when copying this formula across the columns the Sum Range B:B will change automatically to C:C and D:D etc. Using the initial formula, will not change the Sum Range of B:B. Please advise.
From:
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!B:B")))
To:
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!C:C")))
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!D:D")))
Thanks,
L.A.
From:
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!B:B")))
To:
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!C:C")))
=SUMPRODUCT(SUMIF(INDIRECT("'"®IONS&"'!$A:$A"),$A12,INDIRECT("'"®IONS&"'!D:D")))
Thanks,
L.A.