Using R1C1 notation

Hi,

I am using MATCH function to determine which area that should be summed.

Say I have Array

TE FE KR DK LE ST OK LP
-10 -9 -8 -7 -6 -5 -4 -3

And I want to look up value "DK" in array for first row, which would give me a MATCH of 4. Now I want to sum columns 5 to 8, i.e -18.

My plan was to use R1C1 notation to do this.

Planned formula: =SUM(R1C&MATCH("DK", R1C1:R1C8, Exact Match):R1C8)

But it seems impossible to have column index as a variable in R1C1 notation, or am I just doing something wrong?

Help appreciated as always!

Try

Wouldn't it be row 2 you want to sum, surely you can't sum row 1 since it contains the text values for your match..

Try
=SUM(INDEX($A$2:$H$2,MATCH("DK",$A$1:$H$1,0)+1):$H2)

Yes, of course it should be row two.

Beautiful solution, thanks a lot!

This forum is really quick and provides great help!

You're welcome.