Sumif changing sum range

[ReportingRuth]

How do you program a sumif with a variable range?

=SUMIFS($AE$12:$AE$79,$Y$12:$Y$79,C$10)

Instead of a fixed AE column I would like it to vary like this.

=ADDRESS(12,ROWS($A$1:$A1)*2+29)

If I substitute address function in the sumif sum range excel yells at me

Hope you can help,
~RUTH~

 

[Jonmo1]

Welcome to the board...

Try

=SUMIFS(INDEX($AE$12:$IV$79,0,ROWS($A$1:$A1)*2+29),$Y$12:$Y$79,C$10)

Hope that helps.


However, judging by your absolute reference to C$10, it appears you're filling formula to the RIGHT, not DOWN.
In that case, you need to use COLUMNS instead of ROWS.

=SUMIFS(INDEX($AE$12:$IV$79,0,COLUMNS($A$1:A$1)*2+29),$Y$12:$Y$79,C$10)

 

[Aladin Akyurek]

Care to post the formula how does it look like after copying once?

 

[ReportingRuth]

Care to post the formula how does it look like after copying once?

Sure Aladin,

The columns should increase like this inside the sumif.

=ADDRESS(12,ROWS($A$1:$A1)*2+29)
=ADDRESS(12,ROWS($A$1:$A2)*2+29)
=ADDRESS(12,ROWS($A$1:$A3)*2+29)
...

Hope that is clear for you,
~RUTH~

 

[ReportingRuth]

Thanks!
This works

=SUMIFS(INDEX($AE$12:$ZZ$1000,0,ROWS($A$1:$A1)*2-1),$Y$12:$Y$1000,C$10)

Happy Camper,
~RUTH~