Help: Need to calculate Month to date

[mimikat]

Hello, Im hoping someone can help me with this.
Not sure if my title is correct as to what i need… here is a description of what I'm trying to create:

I have sales data for Months of January through December all in different columns. At the end of these months i have a column where i need to calculate month to date.

For ex: If i have sales data for the months of Jan. through March and I enter April sales data it will automatically update the calculation in my last column which i have titled month to date.

Please help =-/

 

[gaz_chops]

Try =sum(column&1st row with Jan:lastcolumnbeforecolumnwithTotalin)

 

[FDibbins]

If you use a regular =SUM() that covers the whole range, it will add the data to the total, as you enter each new month. The sample below only goes to Jun, but it shows the method. Each "row" represents a month a new month's data that was entered.

So, row 2 would be what it looked like in Jan
Row 3 would be what it looked like in Feb
Row 6 would be what it looked like in May
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]Jan[/td][td]Feb[/td][td]Mar[/td][td]Apr[/td][td]May[/td][td]Jun[/td][td]Total[/td][/tr]

[tr][td]

2​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

10​

[/td][/tr]

[tr][td]

3​

[/td][td]

10​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td]

30​

[/td][/tr]

[tr][td]

4​

[/td][td]

10​

[/td][td]

20​

[/td][td]

30​

[/td][td][/td][td][/td][td][/td][td]

60​

[/td][/tr]

[tr][td]

5​

[/td][td]

10​

[/td][td]

20​

[/td][td]

30​

[/td][td]

40​

[/td][td][/td][td][/td][td]

100​

[/td][/tr]

[tr][td]

6​

[/td][td]

10​

[/td][td]

20​

[/td][td]

30​

[/td][td]

40​

[/td][td]

50​

[/td][td][/td][td]

150​

[/td][/tr]
[/table]
H2=SUM(B2:G2)

 

[mimikat]

THANK YOU BOTH SOO MUCH!

Please let me know if you are able to help with the below. Its the last step for this spreadsheet!=-)

So along with the above mentioned columns of January through December (which is where sales data will be entered for each month)…. I am needing to incorporate a sales goal. Which i do have a column for as well. as each months sales data is entered i need to subtract it from a goal which needs to be a function of the formula above for getting the month to date.

So as in the example given in my first post where i have sales data for months Jan through February
every time i add sales data for its corresponding month i need it to be subtracted from "x"sales goal and added automatically to the last "month to date" column… where every new month added is then added along with the previous months which all automatically calculate in the last column "month to date".

PLease let me know if you need clarification.

 

[gaz_chops]

Is it as below?

[TABLE="width: 539"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Goal[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

OK, perhaps this...
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][/tr]
[tr][td]

1​

[/td][td]Jan[/td][td][/td][td]Feb[/td][td][/td][td]Mar[/td][td][/td][td]Apr[/td][td][/td][td]May[/td][td][/td][td]Jun[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Act[/td][td]Goal[/td][td]Act[/td][td]Goal[/td][td]Act[/td][td]Goal[/td][td]Act[/td][td]Goal[/td][td]Act[/td][td]Goal[/td][td]Act[/td][td]Goal[/td][td]Total[/td][/tr]

[tr][td]

3​

[/td][td]

10​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]

10​

[/td][td]

8​

[/td][td]

20​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

5​

[/td][td]

10​

[/td][td]

8​

[/td][td]

20​

[/td][td]

20​

[/td][td]

30​

[/td][td]

35​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

-3​

[/td][/tr]

[tr][td]

6​

[/td][td]

10​

[/td][td]

8​

[/td][td]

20​

[/td][td]

20​

[/td][td]

30​

[/td][td]

35​

[/td][td]

40​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td]

17​

[/td][/tr]

[tr][td]

7​

[/td][td]

10​

[/td][td]

8​

[/td][td]

20​

[/td][td]

20​

[/td][td]

30​

[/td][td]

35​

[/td][td]

40​

[/td][td]

30​

[/td][td]

50​

[/td][td]

55​

[/td][td][/td][td][/td][td]

2​

[/td][/tr]
[/table]
N3=SUMIF($B$2:$M$2,"Act",B3:M3)-SUMIF($B$2:$M$2,"Goal",B3:M3)
copied down

 

[mimikat]

Is it as below?

[TABLE="width: 539"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Goal[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Yes like this except i have the goal titled right before January with the goal amount below.
The goal is the same every month so i only have it once.

 

[mimikat]

 

[mimikat]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Goal[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]MonthToDate[/TD]
[/TR]
[TR]
[TD]100,000[/TD]
[TD]75,000[/TD]
[TD]100,000[/TD]
[TD]125,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So this is an example of exactly how it is formatted….

sorry i forgot to add the sales column which would be titled in A2

 

[mimikat]

Thank you so much. Mine is a bit different but i am going to try to rearrange the formula you have given to match my format.