summing an hlookup
 

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

summing an hlookup
 

=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" wrote:

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

summing an hlookup
 

Brilliant!!!!

Thanks a million bpeltzer!

"bpeltzer" wrote:

=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" wrote:

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