adding cells upto and including a specified cell

  • Thread starter Thread starter Colin
  • Start date Start date
C

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
 
=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)
 
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)))
 
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 said:
=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)
Click to expand...
 
=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:
 
=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 said:
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.
Click to expand...
 
Back
Top