Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing rows using formulas with variables
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing rows using formulas with variables
On Oct 24, 10:53*am, Gateway204 wrote:
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing rows using formulas with variables
On Mon, 24 Oct 2011 08:53:44 -0700 (PDT), Gateway204 wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing 2 columns based on variables | Excel Worksheet Functions | |||
Summing up various Worksheets to one Summary sheet using variables | Excel Discussion (Misc queries) | |||
Summing graphs without differnt independent variables | Charts and Charting in Excel | |||
Variables in formulas | Excel Worksheet Functions | |||
summing cells with variables | Excel Discussion (Misc queries) |