Cumulative Formula

[dapouch]

Hi - sorry for the long post.

I've a spreadsheet with a "calls forecast" in row 3, a "sales forecast" in row 4 etc down the sheet. C3 will be month to date call forecast with G3 - AK3 having call forecast by day for July. C4 will be sales forcast month to date and G4 - Ak4 has sales forecast by day for month - etc. down the spreadsheet.

We use the report daily to see how we are tracking across the month and I need a formula that will populate C3 and C4 (etc) with cumulative Month to date sum of each day's forecast. As each day passes the formula needs to extend by one more cell on the row: 04/07/2012 it will be =Sum(G3:J3) but for 05/07/2012 it will be =sum(G3:K3) if I was doing it manually each day.

I've thought of trying to use the =today() in A1 (unused cell) and =MATCH(A1,G2:AK2, 0) in A2 and then using the value from that somehow but this is where I'm stuck.

Any help on how to proceed would be appreciated.

Thanks

Damian

 

[jono19872006]

I've not read your post thoroughly but i think this should work (note check the ranges to fit your spreadsheet)

=SUM(OFFSET($G$2,1,0,1,MATCH(TODAY(),G2:AK2, 0)))

it basically offsets G2 (Which is your starting point of the month), by the number of days (the date must be consecutive) then creates a range and sums the row below

 

[Robert Mika]

If you have dates in G2
use:
=SUMIF($G$2:$AK$2,"<="&A1,$G$3:$AK$3)
if you do not have use:
=SUM(OFFSET($G$3,0,0,1,DAY(A1)))

 

[dapouch]

Robert,

Second formula works a treat.

Thanks very much.
Jono - I couldn't get yours to work - retuend 0 when it should have been 10,000. Thanks for the assistance though.

 

[Robert Mika]

Robert,

Second formula works a treat.

Thanks very much.
.

You are welcome.Thanks fo feedback.