# 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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

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.

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.

Enter in C2:

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

and copy down.

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.

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

Replies
4
Views
193
Replies
5
Views
110
Replies
3
Views
288
Replies
0
Views
105
Replies
11
Views
381

1,203,071
Messages
6,053,375
Members
444,658
Latest member
lhollingsworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back