• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIF INDIRECT with dynamic Sum Range

larsil

New Member
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("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!B:B")))

To:
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!C:C")))

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!D:D")))

Thanks,

L.A.
 

p45cal

Well-Known Member
Try in cell F12:
Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!A:A"),$A12,OFFSET(INDIRECT("'"&REGIONS&"'!A:A"),0,COLUMN()-1)))
A formula that has no right whatsoever to work… but does.

Aside from that, I've used OFFSET which is a volatile function and tried INDEX instead but struggled.
 
Last edited:

bosco_yip

Excel Ninja
Another option for copy across formula while using INDIRECT function

In Sheet "Cash Forecast daily" , cell F12 change your formula :

From >>
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!F:F")))

To >>
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!R1C"&COLUMN(F1),0)))

Then, copied across right and down

Regards
Bosco
 
Last edited:

larsil

New Member
Another option for copy across formula while using INDIRECT function

In Sheet "Cash Forecast daily" , cell F12 change your formula :

From >>
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!F:F")))

To >>
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!R1C"&COLUMN(F1),0)))

Then, copied across right and down

Regards
Bosco
This formula works too! thanks Bosco for your advice. I love this formula!!
 
Top