summing an hlookup

[mike_vr]

Hi there, wonder if anyone can help?

using an hlookup to find some data, but then would like to sum the data in
the column rather than return one value (and unfortunately there's no total
cell at the end of the data due to coding restraints).

So at the moment I have =HLOOKUP('Page Make-Up'!E3,'Weekly
Update'!A4:BA11,8,FALSE) which returns the value at position 8 all right, but
how would I get the sum of all the values from position 2-8??? eg
=HLOOKUP('Page Make-Up'!E3,'Weekly Update'!A4:BA11,sum(2:8),FALSE) or
something like that!

Any ideas?

Thanks,

Mike

 

[bpeltzer]

=SUM(OFFSET('Weekly Update'!A5:A11,0,MATCH('Page Make-Up'!E3,'Weekly
Update'!A4:BA4,0)-1,8,1))
The MATCH calculates which column you want added up. The OFFSET returns the
array of 8 rows by 1 column. The SUM adds them up.

 

[mike_vr]

Brilliant!!!!

Thanks a million bpeltzer!

=SUM(OFFSET('Weekly Update'!A5:A11,0,MATCH('Page Make-Up'!E3,'Weekly
Update'!A4:BA4,0)-1,8,1))
The MATCH calculates which column you want added up. The OFFSET returns the
array of 8 rows by 1 column. The SUM adds them up.