How to get cumulative figures every month end?

Asked By Nathan Algren 0 points N/A Posted on - 08/01/2012

I have a Microsoft excel sheet which includes payment details to suppliers. I will show you some details as an example. Please see the image below.

I want to get month in to column D at every month end and the month cumulative total to column E. Can anyone tell me a formula?

• Status: Open
• Question Views: 2643
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Geisha Nitta

Go To Solution

Best Answer

Best Answer

Answered By Geisha Nitta 0 points N/A #135372

How to get cumulative figures every month end?

According to my understanding of your requirement basically what you want is the total payment should be appearing in every month end.

Your answer can be arrived using functions of Microsoft Excel. Please follow the below steps.

I will take the same example that you have given. First type “Month” heading as column D & Cumulative payment as Column E.

Then type the following formula in Cell D2 and drag it down to end of data.

=IF(MONTH(A2)=MONTH(A3),"",MONTH(A2))

Now type this formula in Cell E2 drag it down to end of data as previous.

=IF(MONTH(A2)=MONTH(A3),"",SUM(C$2:C2))

Now you can see the cumulative payments have been displayed as follows.

About Geisha Nitta

Questions
1

Answers
55

Best Answers
27

• Vote Up 0 Vote Down

• Posted on - 08/01/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #135373

How to get cumulative figures every month end?

I analyzed your problem and I came up with the solution. What you wanted was to show the month value at each end of the month of the cells in column D and you wanted a cumulative value for each of the month in column E, right? 

Here’s what you should do, key in ‘=IF(MONTH(B4)=MONTH(B5),"",MONTH(B4))’ in column D and then key in ’=IF(IF(D4="",0,(SUMIF(B:B,"<="&B4,C:C)))-SUM($E$1:E3)<=0,"",IF(D4="",0,(SUMIF(B:B,"<="&B4,C:C)))-SUM($E$1:E3))’ in cell E3.

Drag down each of the cells with formulas. That should do the trick. DON'T FORGET: cell E1 and E2 should be empty.

Hope this helps.

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/02/2012
• Question Category: MS Excel