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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,505
Messages
5,832,117
Members
430,111
Latest member
Francis Xavier

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top