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

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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.
 

Some videos you may like

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
Joined
Mar 13, 2002
Messages
555
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 11, 2002
Messages
148
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
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
Joined
Jul 11, 2002
Messages
148
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
Joined
Jul 21, 2002
Messages
73,092
I thought you only wanted the total on the last day of the month. Your formula gives month to date.

Glad to have helped.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,915
Messages
5,575,018
Members
412,634
Latest member
Sumanmathew
Top