Sum range of cells for specific lines

[Dantz]

I want a formula that looks up for a specific text in a table, and then add a controlled number of columns in that text's row.
=-SUM(OFFSET('IS Act'!$C$11, 0, 0, 1, CONTROL!B5))
Using the formula above, I want 'IS Act'!$C$11 to be determined by the identity of the text. In other words if the text is not in C11 but C77 then the formula above should read
=-SUM(OFFSET('IS Act'!$C$77, 0, 0, 1, CONTROL!B5))
Note CONTROL!B5 controls how many columns I want eveluated.

I had tried to use
=ADDRESS(MATCH($B$37,'IS Act'!$B:$B,0),3,,1,"IS Act")

In this case my text for the lookup is in B37, and once this is found in IS Act I want the formula to then sum the number of columns specified by Control!B5.
Thanks.

 

[chrism216]

Hi.

You were quite close. In the following formula i'll assume the text you need to find is in column A:

=SUM(OFFSET(OFFSET('IS Act'!$A$1,MATCH(B37,'IS ACT'!A:A,0)-1,1),,,1,'Control'!B5))

Hope it helps!

Cheers,

Chris

 

[chrism216]

The last formula was overly complicated, so ill post again:

=SUM(OFFSET('IS Act'!$A$1,MATCH(B37,'IS ACT'!A:A,0)-1,2,1,'Control'!B5))

Offset explained:
1st argument (REF): You start on A1.
2nd argumet (ROWS): you find the row on whichyou match the value of b37. -1 because of counting reasons.
3rd argument (COLUMNS): you move 2 to the right, since column "C" is 2 to the right of "A".
4th argument (HEIGHT): 1 because you are only summing over 1 row.
5th argument (WIDTH): this is the parameter controlled by B5, specifying how many columns you want to include in your sum.

Cheers,

Chris

 

[Dantz]

Thanks chrism216, both formulae worked.
Be blessed.