Formula to sum

[dbrasel]

I'm working on a formula that will sum certain cells on one spreadsheet based on a value in a different cell on a different spreadsheet. the formula below is what I've come up with so far:

=IF('Table of Contents'!F1=1,SUM(Roseville!E26:E26),IF('Table of Contents'!$F$1=2,SUM(Roseville!E26:F26)))

as I will need a total of 12 if's (F1 =1, F1 = 2 all the way to F1=12,SUM (Roseville!E26:P26), I was wondering if there were a neater way of creating this formula. I have Excel 2007.

as always, thanks for the help!


Deb

 

[baitmaster]

I would look at INDIRECT for this. You can use it to concatenate text and numbers, based on entries in other cells, to form a reference to your required range

e.g.

=SUM(INDIRECT("A"&B1&":A"&C1))

will sum all cells in column A, between row [value in B1] and row [value in C1]

Use this approach, along with a formula that changes the Letter as you want too (e.g. based on a VLOOKUP), and it should do what you want

 

[jim may]

Add the remaining 8 references the Roseville!$E26:P26))

=CHOOSE('Table of Contents'!F1,SUM(Roseville!$E26:E26),SUM(Roseville!$E26:F26),SUM(Roseville!$E26:G26),SUM(Roseville!$E26:H26))

 

[delaneyjm]

You could use something like:

=SUM(E26:INDEX(E26:P26,1,F1))

 

[MrKowz]

Perhaps:

=SUM(INDIRECT("E26:"&ADDRESS(26,5+'Table of Contents'!$F$1))

 

[dbrasel]

thank you for such a quick response! I went with delaneyjm's answer, it works like a champ! this board is awesome, I really appreciate the help!

Deb