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 balance
I 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

Report •

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 •

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):
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.

1 2
2 3 1 2
3 1 1 1 2
4 2 2 2 4
5 3 3 3 6

1 3
2 3 4 1
3 1 1 1 3
4 2 2 2 6
5 3 3 3 9

I fear I have now gone overboard and been too wordy, Thank you so much for your help.

Report •

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

1 1 3
2 3 4 1

Report •

Related Solutions

May 13, 2011 at 11:45:41
First, if you had read the very blue, very bold line at the end of my post, which pretty clearly states:

Click Here Before Posting Data or VBA Code

you wouldn't have had trouble lining up your data. The instructions for posting data in this forum can be found via that link.

Second, did you try the solution I offered?

In your OP, you basically asked for a method that sums a variable range, with the column determined by the location of the minimum value in a range. That is what my suggestion does.

It finds the MIN value in A1:D1 and uses that column as the "x" in =SUM(A1:x3).

Once you play with my example and understand how it works (the Excel Help files can assist you with this) you should be able to adapt it for your use.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 16, 2011 at 08:53:28
Got it. Thank you.

Report •

Ask Question