Setting column reference in sumproduct

[jamie_in_nj]

I am using the sumproduct function in a worksheet to show some present values; i.e.,

<TABLE style="WIDTH: 130pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=174 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 77pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=103 height=17>Amt_Yrs</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=71>PV (EGP)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1,194,386</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2,475,460</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3</TD><TD class=xl74 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3,827,974</TD></TR></TBODY></TABLE>
etc.

where the formula in B2 is
=SUMPRODUCT(GAAP!$C$111:$C$111,GAAP!$C$112:$C$112)

and the formula in B3 is
=SUMPRODUCT(GAAP!$C$111:$D$111,GAAP!$C$112:$D$112)

and the formula in B4 is
=SUMPRODUCT(GAAP!$C$111:$E$111,GAAP!$C$112:$E$112)

etc.

Is there a way to set the end column (i.e., C, D, E, etc.) to be dependent on the values in column A (i.e., 1, 2, and 3)? For the first three I just set the C, D and E manually, but that's pretty time-consuming.

Thanks!

 

[Domenic]

Let's say that the range ends at Column Z, try...

B2, copied down:

=SUMPRODUCT(GAAP!$C$111:INDEX(GAAP!$C$111:$Z$111,ROWS(B$2:B2)),GAAP!$C$112:INDEX(GAAP!$C$112:$Z$112,ROWS(B$2:B2)))

Change the range accordingly.

 

[jamie_in_nj]

Very cool. That works. I changed the Z to AY, since that is the last column of my values.

So ROWS(B$2:B2) returns 1, ROWS(B$2:B3) returns 2, etc, and

INDEX(GAAP!$C$111:$Z$111,1) returns GAAP!$C$111:$C$111
and
INDEX(GAAP!$C$111:$Z$111,2) returns GAAP!$C$111:$D$111
etc?

 

[Domenic]

So ROWS(B$2:B2) returns 1, ROWS(B$2:B3) returns 2, etc,

Yes...

INDEX(GAAP!$C$111:$Z$111,1) returns GAAP!$C$111:$C$111
and
INDEX(GAAP!$C$111:$Z$111,2) returns GAAP!$C$111:$D$111
etc?

Actually, INDEX(GAAP!$C$111:$Z$111,1) returns GAAP!$C$111. However, GAAP!$C$111:INDEX(GAAP!$C$111:$Z$111,1) returns GAAP!$C$111:$C$111, and GAAP!$C$111:INDEX(GAAP!$C$111:$Z$111,2) returns GAAP!$C$111:$D$111, and so on.

 

[jamie_in_nj]

OK, that makes sense. Thanks for your help, and for the explanation!