Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLookUp | Excel Worksheet Functions | |||
Hlookup for row 3 and 4 | Excel Worksheet Functions | |||
PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
Summing HLookup Values | Excel Worksheet Functions | |||
Hlookup/max value? | Excel Worksheet Functions |