## How to get cumulative figures every month end?

Asked By 0 points N/A Posted on -

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?

SHARE
Answered By 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.

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.

Answered By 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.