# Sum range of cells for specific lines

#### Dantz

##### New Member
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.

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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### chrism216

##### Board Regular
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

##### Board Regular
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

##### New Member
Thanks chrism216, both formulae worked.
Be blessed.

Replies
2
Views
52
Replies
9
Views
57
Replies
6
Views
58
Replies
3
Views
72
Replies
15
Views
204