# Cumulative Formula

#### dapouch

##### New Member
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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

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

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,

Second formula works a treat.

Thanks very much.
.
You are welcome.Thanks fo feedback.

Replies
3
Views
808
Replies
8
Views
370
Replies
17
Views
929
Replies
6
Views
376
Replies
5
Views
361

1,203,064
Messages
6,053,320
Members
444,653
Latest member
Curdood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back