Replace column letter with letter in other cell

[Okoth]

Hi,

I have 5 columns, 5 rows like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]a[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to use SUM.IF to get the value from the column that is determined by the value in cell E1. The value in E1 is the result of a formula.

I'm kind of looking for a formula like this =SOM.ALS(A1:A5,A1,E1&"1":E1&"5") but of course that doesn't work.

How can I do this?
Thanks

 

[jim may]

You may be getting the "$1" error due to the full address of "$1:$1048576" << This works ONLY in xl2007 and beyond! If you are xl2003 or prior change the 1048576 to the approx 36K of the total rows in your sheet. Maybe this is your prob... Good luck Jim

 

[Okoth]

Thanks for the reply, Jim. I'm using Excel 2010 at the moment so that shouldn't be the problem.

And then I followed your advice and lowered the number to 3600. Worked!

I'm going to test it and et you know.

Mike and Jim, thank you both for the great help. This week I don't have time anymore to analyse the formula Mike gave, but I certainly want to know how it works! I'm just not working enough with Excel to become a pro.

Thanks again!