Sum range of cells for specific lines

Dantz

New Member
Joined
Oct 8, 2014
Messages
2
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.
 

Some videos you may like

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
Joined
Aug 6, 2013
Messages
211
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
Joined
Aug 6, 2013
Messages
211
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top