# Auto Calculation Per Month

1. ## Auto Calculation Per Month

Hello, I'm trying to create a column that will auto calculate all values that are within the same month.

In column A are dates (that have no pattern), column B are values, and I would like column C the add up all the values in B that are in the same month

Is this possible?

I've attach and example of what I'm trying to achieve
ForForum.xls  Register To Reply

2. ## Re: Auto Calculation Per Month

Put in Column C in each end of the month:

=SUM((MONTH(\$A\$2:\$A\$40)=MONTH(A5))*(\$B\$2:\$B\$40)

Array Formula, you need to press CTRL-SHIFT-ENTER button together, and then copied down  Register To Reply

3. ## Re: Auto Calculation Per Month

You can also use SUMPRODUCT function
Formula:  `Please Login or Register  to view this content.`
Array formulas can slow-down your worksheet a bit if your ranges are BIG.  Register To Reply

4. ## Re: Auto Calculation Per Month

How about trying a Pivot Table that groups by month?  Register To Reply

5. ## Re: Auto Calculation Per Month

=IF(MONTH(A2)=MONTH(A3),"",SUMPRODUCT((B\$2:B2)*(YEAR(A\$2:A2)=YEAR(A2))*(MONTH(A\$2:A2)=MONTH(A2))))
Enter the Formula in "C1" and copy towards the down  Register To Reply

6. ## Re: Auto Calculation Per Month

I've never used array formulas before so I'm in process of learning about them now

However your formula isn't quite what I am trying to achieve.
I was hoping so I wouldn't need to find each end of the month row to enter in the formula, it would instead auto detect it was the last date of that month.

Also my mistake for quickly entering dates for this forum version. In my spreadsheet it contains more than 1 year, thus has the same month a few times making the above formula not work as needed
Though I think selecting the range for Col A dependant on the year will be fine for now

Also I've never used pivot tables before, I'll look into it, thank you  Register To Reply

7. ## Re: Auto Calculation Per Month

Ah, missed your post nflsales. Thank you. After a quick look it seems to be exactly what I was looking for. Thanks  Register To Reply

8. ## Re: Auto Calculation Per Month

Try this in C2 and drag down.

=IF(MONTH(A2)<>MONTH(A3),SUMPRODUCT((MONTH(\$A\$2:\$A\$41)=MONTH(\$A2))*(\$B\$2:\$B\$41)),"")  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1