Summing rows using formulas with variables


G

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.
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top