How to get cumulative figures every month end?

Asked By 0 points N/A Posted on -
qa-featured

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.

MS Excel

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?

SHARE
Best Answer by Geisha Nitta
Best Answer
Best Answer
Answered By 0 points N/A #135372

How to get cumulative figures every month end?

qa-featured

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

MS Excel Function

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

Set formula in Excel Sheet

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

Outcome of the formula
Answered By 0 points N/A #135373

How to get cumulative figures every month end?

qa-featured

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.

Login/Register to Answer

Related Questions