Summing rows using formulas with variables

[Gateway204]

Long story short- I have a range, columns are years, rows are
numerical data. I want to be able to enter a start year and an end
year into two different cells (entirely separate from the range) that
feed into a formula that then sums whichever type of data a row
happens to be between those two years. By adding a row at the bottom
of the range that contains A, B, C and so on (and they are in the A,
B, C, etc columns; was there a better way to return the column letter?
I tried COLUMN but only got numerical values, not the letters.) I was
able to return "B", as the first part of a cell reference. The first
data I am trying to sum is in Row 13, so I attempting to sum from B13
to B17. I got the "B" by doing HLOOKUP(B2,B12:AE20,9), but when I try
to add the 13 to make the formula reference cell B13, I get an error
message. How do I make the formula look at cell B13? I am weak on
macros, and know nothing about VBA. Thanks in advance.

 

[Don Guillett]

Long story short- I have a range, columns are years, rows are
numerical data. I want to be able to enter a start year and an end
year into two different cells (entirely separate from the range) that
feed into a formula that then sums whichever type of data a row
happens to be between those two years. By adding a row at the bottom
of the range that contains A, B, C and so on (and they are in the A,
B, C, etc columns; was there a better way to return the column letter?
I tried COLUMN but only got numerical values, not the letters.) I was
able to return "B", as the first part of a cell reference. The first
data I am trying to sum is in Row 13, so I attempting to sum from B13
to B17. I got the "B" by doing HLOOKUP(B2,B12:AE20,9), but when I try
to add the 13 to make the formula reference cell B13, I get an error
message. How do I make the formula look at cell B13? I am weak on
macros, and know nothing about VBA. Thanks in advance.

how about just using a SUMPRODUCT formula
=sumproduct((a2:a22>=b1)*(a2:a22<b2)*b2:b22)
modify to suit and do NOT use entire columns.

 

[Ron Rosenfeld]

Long story short- I have a range, columns are years, rows are
numerical data. I want to be able to enter a start year and an end
year into two different cells (entirely separate from the range) that
feed into a formula that then sums whichever type of data a row
happens to be between those two years. By adding a row at the bottom
of the range that contains A, B, C and so on (and they are in the A,
B, C, etc columns; was there a better way to return the column letter?
I tried COLUMN but only got numerical values, not the letters.) I was
able to return "B", as the first part of a cell reference. The first
data I am trying to sum is in Row 13, so I attempting to sum from B13
to B17. I got the "B" by doing HLOOKUP(B2,B12:AE20,9), but when I try
to add the 13 to make the formula reference cell B13, I get an error
message. How do I make the formula look at cell B13? I am weak on
macros, and know nothing about VBA. Thanks in advance.

I'm not sure I understand your setup of data, and I do not understand why you want to return the letters relating to the columns which contain the years.

If your years are in columns, then possibly your setup is like:

B1: Year 1 (e.g. 2000)
C1: Year 2 (2001)
D1: Year 3 (2002)
....

And if your data is in rows starting with row 2 and assume a label in A2 with actual data in B2 rightward.

If that is the case, you can sum that part of the row that starts at a StartYear column and ends and an EndYear column with the following formula:

This formula must be **array-entered**:

=SUM(OFFSET($A$1,RowToSum,MATCH(
StartYear,$1:$1,0)-1,1,EndYear-StartYear+1))

StartYear, EndYear and RowToSum are all in individual cells someplace on your worksheet.
RowToSum is the first row of data, Not the row number in the worksheet, but you could equally well MATCH a label similar to the way we used MATCH to determine the StartYear.
If you need to specify multiple rows, that can be done by adjusting the [height] parameter in the OFFSET function similarly to the adjustment we did for the [width] parameter.

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If your layout is different, you will need to be more explicit.