How to sum automatically month's result when month changes?

[Sir Vili]

Hi masters.

In Col A I have dates with changing dates and months.

In Col B I have numbers.

In Col C I want some formula that calculates total of month at that row in Col C which is last day of month.

Best regards Sir Vili.

 

[sen_edp]

Hello Sir Villi

An easy way to do it
insert a new column ,name it month and enter
the following formula all the way down
=month(a2)
and you get the month number
after this you go to main menu,data,subtotals
and put in each change of month to sum the amount.

hth

 

[Andrew Poulsom]

Assuming A2:A4 contains dates, B2:B4 contains the values and cell C1 contains the month number, enter the following array formula in any cell in the range C2:C4:

=SUM(IF(MONTH($A$2:$A$4)=C$1,$B$2:$B$4))

Press Ctrl+Shift+Enter (not just Enter) after typing.

 

[Sir Vili]

Thank You for your replies sen_edp and Anrew.

This was not exactly I want. Here more specific:

In A2: 02.05.2002 and in B2 10.
In A3: 12.05.2002 and in B3 20.
In A4: 31.05.2002 and in B4 30.
In A5: 02.06.2002 and in B5 60.

I want:
in C2 0 (formula shows zero)
in C3 0 (formula shows zero)
in C4 sum of month (may), because there is change of month and new month starts in A5.

Sir Vili.

 

[Andrew Poulsom]

Enter in C2:

=IF(MONTH(A2+1)>MONTH(A2),SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(A2))*($B$2:$B$6)),"")

and copy down.

 

[Sir Vili]

Thank You Andrew again.

It did not work for correctly, because it did not sum next month (jun) automatically. But from Your formula I got idea and I made some changes and now it works correctly.

This is what I made (new formula):

=IF(MONTH(A2)<>MONTH(A3),SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(A2))*($B$2:$B$6)),"")

Thank You very much for idea.

Best regards Sir Vili.

 

[Andrew Poulsom]

I thought you only wanted the total on the last day of the month. Your formula gives month to date.

Glad to have helped.