SUMIF INDIRECT with dynamic Sum Range

[larsil]

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 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")))

Thanks,

L.A.

 

[VenA]

You are on a Dutch forum. Ask the question in Dutch. If you have also asked the question in other forums, report that too.

Indirect in this formula is unnecessary. And otherwise place the file. Or an example of it.

 

[larsil]

Hi Vena bedankt voor je bericht. Mijn Nederlands is niet zo geweldig, vandaar leg ik het uit in het Engels.

Mijn vraag is hoe kan ik een dynamische formule maken zodat ik de formule gemakkelijk van cel B1 naar cel B2 en B2 kan slepen. Mijn verwachting is dat de sum_range INDIRECT ("'" & REGIO'S & "'! B: B") zich automatisch aanpast aan INDIRECT ("'" & REGIO'S & "'! C: C"), INDIRECT ("'" & REGIO'S & "'! D: D ") enz.


Vanuit Cell B1:
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!B:B")))

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

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

Bedankt voor je hulp!

L.A.

 

[Haije]

larsil,

kun je een (voorbeeld van je) Excelbestand posten?

 

[larsil]

Hi Haije,

Ja zeker. In de bijlage kunt u het bestand vinden.



Met vriendelijke groet,

L.A.

 

[Haije]

larsil,

wat werkt er volgens jou niet goed in je bijlage?

 

[larsil]

Hi Haije, de formule werkt prima, maar ik moet deze formule naar alle kolommen kopiëren, voor meer dan 100 kolommen. Wanneer ik de formule naar een andere kolom kopieer, verandert de "sum_range" --> (INDIRECT ("'" & REGIO'S & "'! F: F")) niet. Wat ik wil is dat de range "F: F" aangepast moet worden naar "G: G", "H: H", "I: I" etc. Ik hoop dat dat duidelijk is.

 

[larsil]

Vanwege de manier waarop de formule is samengesteld, zijn de kolomverwijzingen tekstreeksen, geen daadwerkelijke verwijzingen. Hierdoor zijn alle referenties absoluut.

 

[jkpieterse]

SUMIFS nesten in SUMPRODUCT en dan ook nog eens als argument een volatile functie gebruiken. Dat is vragen om een uiterst traag rekenmodel.

 

[larsil]

Heeft u dan een oplossing?

 

[jkpieterse]

Een manier zou kunnen zijn om niet voor iedere "afdeling" een apart tabblad te maken, maar 1 tabel met alle afdelingen met een extra kolom met die AAA, BBB of CCC's erin. Je hebt teveel een mix tussen mooie uitvoer en invoer en calculatie gemaakt. Dan is het vervolgens lastig om een overzicht te genereren zoals je merkt.