# excel sum a variable number of columns Microsoft Office 2007 professional (aca...
May 12, 2011 at 20:10:34
Specs: Windows 7, i5
 I have a financial spreadsheet. Each Column represents a month.Using a MIN function I know that the 8th month is the lowest cash balanceI wish to sum up for several (about 50) rows the numbers in months 1 through 8 (in the current case, columns c through j).When other numbers in the spreadsheet change, and the MIN function returns month 10 (for example) indicating that the lowest cash balance month is 10th, I would want the sum to now be months 1 through 10 (columns C through L)I'm currently using excel 2007 See More: excel sum a variable number of columns

#1 May 12, 2011 at 21:32:08
 First, it would have been nice if you had actually asked for some help instead of just telling us what you want and expecting us to come up with a solution for you.That said, I'm in a giving mood, so I'll offer something that should work...You say you want to sum "about 50 rows" but you don't say which rows or which Row is the source for the MIN value, so the best I can offer is an example that should work for you, with modifications based on your actual data.Let's say you have this table:``` A B C D 1 10 12 9 15 2 23 8 6 45 3 4 3 8 2 ```Let's assume that you are using Row 1 as the source for the MIN value. In the example above, that would mean that you would want to SUM(A1:C3) since MID(A1:D1) is 9.This formula will do just that:=SUM(A1:INDEX(A1:D3, 3, MATCH(MIN(A1:D1),A1:D1,0)))I'll leave it up to you to figure out how it works so that you can modify it to fit your needs.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
May 13, 2011 at 05:28:57
 I apologize for not being explicit, and for not asking a question. Thanks so much for helping a newbie and trying to read my mind. May I try again?I'm essentially trying to do the following (but of course, excel won't let me):=sum(A2:x2)=sum(A3:x3)etc.In this case, "x" is a a variable from 1 to 3,which I got from a =match(min(a1:c1),0)))so, if the minimum of row one is the 2nd column, sum columns a,b for each row.if the minimum of row one is the 3rd column, sum columns a,b,c for each row.As am example In both cases, cell a1 has used =match(min) to find which column has the minimum number (column B in the former and column C in the latter)In column D I have the sum of each of the rows. In the former example I've used =sum(a3:b3), =SUM(A4:b4, =sum(a5:b5 etc. In the latter example I've used =sum(a3:c3), =sum(a4:c4), etc. I'm hoping you can help me substitute a variable in the second parameter. A B C D1 2 2 3 1 2 3 1 1 1 24 2 2 2 45 3 3 3 6 A B C D1 3 2 3 4 1 3 1 1 1 34 2 2 2 65 3 3 3 9I fear I have now gone overboard and been too wordy, Thank you so much for your help.

Report •

#3
May 13, 2011 at 05:32:36
 Oops, the formatting didn't work as expected (I should have previewed). Please shift the column headers "A B C D" one over to the right. ie A B C D1 1 32 3 4 1 etc.

Report •

Related Solutions

#4 May 13, 2011 at 11:45:41 