adding cells upto and including a specified cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Colin - 21 May 2008 05:40 GMT

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

Signature

Regards
Colin

Reply to this Message

Stefi - 21 May 2008 07:36 GMT

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

„Colin” ezt írta:

> Hi
>
[quoted text clipped - 18 lines]
>
> Any help would be gretly appreciated

Reply to this Message

Colin - 22 May 2008 03:20 GMT

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

Signature

Regards
Colin

> =SUM(OFFSET(A4,0,0,1,MATCH($B$1,3:3,0)))
> Regards,
[quoted text clipped - 24 lines]
> >
> > Any help would be gretly appreciated

Reply to this Message

Stefi - 22 May 2008 08:21 GMT

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

> Hi Stefi
>
[quoted text clipped - 37 lines]
> > >
> > > Any help would be gretly appreciated

Reply to this Message

Bob Phillips - 21 May 2008 11:20 GMT

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

Signature

---
HTH

Bob

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

> Hi
>
[quoted text clipped - 20 lines]
>
> Any help would be gretly appreciated

Reply to this Message

Colin - 22 May 2008 03:24 GMT

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.

Signature

Regards
Colin

> =SUM(A4:INDEX(4:4,MATCH(B1,3:3,0)))
>
[quoted text clipped - 22 lines]
> >
> > Any help would be gretly appreciated

Reply to this Message

Bob Phillips - 22 May 2008 08:51 GMT

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

Signature

---
HTH

Bob

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

> Hi Bob
>
[quoted text clipped - 34 lines]
>> >
>> > Any help would be gretly appreciated

Reply to this Message