Using sum functions with Hlookup . Help Required !

[tglancy]

I currently have the following function code :- It works but is there a far tidier way of doing this with a SUM (or similar) function ? Many Many thanks.

=HLOOKUP("tot_prem",'Column Output'!A:BW,8,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,9,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,10,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,11,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,12,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,13,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,14,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,15,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,16,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,17,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,18,FALSE)/1000+HLOOKUP("tot_prem",'Column Output'!A:BW,19,FALSE)/1000

 

[jimboy]

What about naming the range "column_output" and then putting in the formula;

=sum(column_output)/1000

hope this helps.

 

[Dave Patton]

A few words describing your challenge is often very useful.

If you know where the information is, try

=SUM('Column Output'!C8:C19)/1000

If the criteria is in just one unspecified heading, try

=SUM(OFFSET('Column Output'!A1,7,MATCH("tot_prem",'Column Output'!A1:G1,0)-1,12,1))/1000

 

[tglancy]

Thanks Dave and Jimboy..

I should have described this further. I need to retain the hlookup facility as the 'Column Output' Worksheet is prone to change. Is there a way that I can sum the results of an HLOOKUP with less code than I specified earlier ?

Thanks again for your help.

 

[Dave Patton]

On 2002-09-19 07:09, tglancy wrote:
Thanks Dave and Jimboy..

I should have described this further. I need to retain the hlookup facility as the 'Column Output' Worksheet is prone to change. Is there a way that I can sum the results of an HLOOKUP with less code than I specified earlier ?

Thanks again for your help.

try the Match Offset combination described above

1. with minor changes

=HLOOKUP("Tot_Prem",A1:BW2,2,0)/1000

2. VBA instead of direct formulas

etc
This message was edited by Dave Patton on 2002-09-20 07:12