Sum columns up to vlookup value
 

I have a workbook with multiple wooksheets. On worksheet 1 F1 is a month/yr.
ie. Feb 08 (text). I want to do a Vlookup on E6 to sheet '2008 Budget' and
sum columns up to column labeled Feb 08. Next month I will want the sum of
Jan 08, Feb 08, Mar 08. etc. Each month adding a column to the sum.
Looking at the following formula I want it to return the sum of B:M up to the
column matching the value in F1.

=VLOOKUP(E6,'2008 Budget'!$A$1:$M$105,('2008 Budget'!Sum(B5:C5)),FALSE)

Can this be done?

Sum columns up to vlookup value
 

The formula I use considers the following Facts:
Formula is sitting on E6
Lookup Value is on F1
Value on F1 is 02/29/2008 formatted for display as Feb 08
In Sheet 2008 Budget I have Months going accross in Row1 Columns A thru L
Values in that row are 01/31/2008, 02/29/2008, 03/31/2008, etc .
The Values to be added are in Row 5 Columns B thru M
The values in row 5 a 10,20,30,40,50, . . .
The Formula result when F1 = 03/31/2008 (Displayed as Mar 08) is 60 which is
the sum of 10+20+30.
The formula is:
=SUM(OFFSET('2008 Budget'!B5:M5,0,0,1,MATCH(F1,'2008 Budget'!A1:L1)))

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Sinkguy1" wrote:

I have a workbook with multiple wooksheets. On worksheet 1 F1 is a month/yr.
ie. Feb 08 (text). I want to do a Vlookup on E6 to sheet '2008 Budget' and
sum columns up to column labeled Feb 08. Next month I will want the sum of
Jan 08, Feb 08, Mar 08. etc. Each month adding a column to the sum.
Looking at the following formula I want it to return the sum of B:M up to the
column matching the value in F1.

=VLOOKUP(E6,'2008 Budget'!$A$1:$M$105,('2008 Budget'!Sum(B5:C5)),FALSE)

Can this be done?

Sum columns up to vlookup value
 

=SUM(OFFSET('2008 Budget'!B5:M5,0,0,1,MATCH(F1,'2008 Budget'!B5:M5)))
This formula returns the total of row 5. If I use A5:L5 i get the same
result. If i use row a I get #N/A. Can you help me understand what I am
doing wrong?
Also is there a way to lookup a value in E6, find it in 2008 Budget column A
and then sum columns up to F1?


"Michael" wrote:

The formula I use considers the following Facts:
Formula is sitting on E6
Lookup Value is on F1
Value on F1 is 02/29/2008 formatted for display as Feb 08
In Sheet 2008 Budget I have Months going accross in Row1 Columns A thru L
Values in that row are 01/31/2008, 02/29/2008, 03/31/2008, etc .
The Values to be added are in Row 5 Columns B thru M
The values in row 5 a 10,20,30,40,50, . . .
The Formula result when F1 = 03/31/2008 (Displayed as Mar 08) is 60 which is
the sum of 10+20+30.
The formula is:
=SUM(OFFSET('2008 Budget'!B5:M5,0,0,1,MATCH(F1,'2008 Budget'!A1:L1)))

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Sinkguy1" wrote:

I have a workbook with multiple wooksheets. On worksheet 1 F1 is a month/yr.
ie. Feb 08 (text). I want to do a Vlookup on E6 to sheet '2008 Budget' and
sum columns up to column labeled Feb 08. Next month I will want the sum of
Jan 08, Feb 08, Mar 08. etc. Each month adding a column to the sum.
Looking at the following formula I want it to return the sum of B:M up to the
column matching the value in F1.

=VLOOKUP(E6,'2008 Budget'!$A$1:$M$105,('2008 Budget'!Sum(B5:C5)),FALSE)

Can this be done?

Sum columns up to vlookup value
 

I found my problem. My data in row 1 was not formated properly to match
between sheets. Thank you for your help!

Larry

"Sinkguy1" wrote:

=SUM(OFFSET('2008 Budget'!B5:M5,0,0,1,MATCH(F1,'2008 Budget'!B5:M5)))
This formula returns the total of row 5. If I use A5:L5 i get the same
result. If i use row a I get #N/A. Can you help me understand what I am
doing wrong?
Also is there a way to lookup a value in E6, find it in 2008 Budget column A
and then sum columns up to F1?


"Michael" wrote:

The formula I use considers the following Facts:
Formula is sitting on E6
Lookup Value is on F1
Value on F1 is 02/29/2008 formatted for display as Feb 08
In Sheet 2008 Budget I have Months going accross in Row1 Columns A thru L
Values in that row are 01/31/2008, 02/29/2008, 03/31/2008, etc .
The Values to be added are in Row 5 Columns B thru M
The values in row 5 a 10,20,30,40,50, . . .
The Formula result when F1 = 03/31/2008 (Displayed as Mar 08) is 60 which is
the sum of 10+20+30.
The formula is:
=SUM(OFFSET('2008 Budget'!B5:M5,0,0,1,MATCH(F1,'2008 Budget'!A1:L1)))

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Sinkguy1" wrote:

I have a workbook with multiple wooksheets. On worksheet 1 F1 is a month/yr.
ie. Feb 08 (text). I want to do a Vlookup on E6 to sheet '2008 Budget' and
sum columns up to column labeled Feb 08. Next month I will want the sum of
Jan 08, Feb 08, Mar 08. etc. Each month adding a column to the sum.
Looking at the following formula I want it to return the sum of B:M up to the
column matching the value in F1.

=VLOOKUP(E6,'2008 Budget'!$A$1:$M$105,('2008 Budget'!Sum(B5:C5)),FALSE)

Can this be done?