lookup a value and sum a corresponding dynamic range

[doug firr]

Trying to create a nested formula and am struggling. What I need to do is find a value in a range and then sum the corresponding row. The row grows each month.
This is the data table that needs to be looked up

	july	august	september	october	november
tim	123	456	789	101	212
david	987	654	321	123	456
john	123	456	789	987	765
peter	432	123	456	789	101

<tbody>
</tbody>

I need a formula that looks up a given name and then sums the total for that row. The row grows each month and I'd like to have this automatically update when the new date column is added.

Any pointers?

 

[barry houdini]

Assuming names in A2:A5 and data in B2:Z5 try this formula to sum data for "john" (assuming that will be only 1 row)

=SUM(INDEX(B2:Z5,MATCH("john",A2:B5,0),0))

That will sum data up to column Z, you could extend that further if required. Lookup value "john" could be a cell reference if required

 

[hurgadion]

Hi,
let Your Data be in Columns A:F, exactly in A1:F5. If in H2 is a given name, in I2 the following formula is enough:

=SUM(INDIRECT(ADDRESS(MATCH(H2,A1:A5,0),2)&":"&ADDRESS(MATCH(H2,A1:A5,0),6)))

Best regards.