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

#### Sir Vili

##### Board Regular
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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### sen_edp

##### Well-known Member
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

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP

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

##### Board Regular
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

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

Replies
10
Views
368
Replies
6
Views
140
Replies
2
Views
79
Replies
1
Views
88
Replies
3
Views
50