adding cells upto and including a specified cell

[Colin]

Hi

Im trying to work out what formula i can use to sum the figures in row 4
upto and including the specified month in cell b1 and display the result in
b6.

a b c d e f
1 month apr-08
2
3 jan-08 feb-08 mar-08 apr-08 may-08 june-08
4 100 100 150 600 680 265
5
6 YTD 950

eg if cell b1=apr-08 then cell b6 should = 950 (sum(a4:d4))
if cell b1=mar-08 then cell b6 should = 350 (sum(a4:c4))

I have assumed i will need to use some sort of LOOKUP formula to find b1 in
row 3 but once i have established that part i cant work out the formula to
sum row4 upto the selected cell.

Any help would be gretly appreciated

 

[Stefi]

=SUM(OFFSET(A4,0,0,1,MATCH($B$1,3:3,0)))
Regards,
Stefi


„Colin†ezt írta:

 

[Bob Phillips]

=SUM(A4:INDEX(4:4,MATCH(B1,3:3,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Colin]

Hi Stefi

Thanks heaps for the reply.

That has solved half of my problem. My next problem is the data in the
example i created what if rows 3 and 4 are in sheet2 and rows 1 and 6 are in
sheet1. can i use the formula you provided to reference different sheets.

I tried something similar to the following but it didnt seem to want to sum
data in a seperate sheet.

=SUM(OFFSET(sheet2!A4,0,0,1,MATCH(sheet1!$B$1,sheet2!A3:sheet2!F3,0)))

 

[Colin]

Hi Bob

Thanks for your reply

How would i change your formula if the data in rows 1 and 6 were in sheet1
and rows 3 and 4 were in sheet2. i had a play around and i couldnt get the
formula to sum data that was in a different sheet.
--
Regards
Colin

=SUM(A4:INDEX(4:4,MATCH(B1,3:3,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stefi]

=SUM(OFFSET(Sheet2!A4,0,0,1,MATCH($B$1,Sheet2!3:3,0)))
Why don't you ask your whole question at once?
Regards,
Stefi


„Colin†ezt írta:

 

[Bob Phillips]

=SUM(Sheet2!A4:INDEX(Sheet2!4:4,MATCH(B1,Sheet2!3:3,0)))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob

Thanks for your reply

How would i change your formula if the data in rows 1 and 6 were in sheet1
and rows 3 and 4 were in sheet2. i had a play around and i couldnt get the
formula to sum data that was in a different sheet.