=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)



"Colin" wrote in message
...
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



"Bob Phillips" wrote:

=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" wrote in message
...
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

--
Regards
Colin