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
Last edited by Smally; 06-16-2014 at 01:47 AM.
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
Last edited by azumi; 06-16-2014 at 12:07 AM.
You can also use SUMPRODUCT functionArray formulas can slow-down your worksheet a bit if your ranges are BIG.Formula:
Please Login or Register to view this content.
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
How about trying a Pivot Table that groups by month?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
=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
Thanks for your reply
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
Ah, missed your post nflsales. Thank you. After a quick look it seems to be exactly what I was looking for. Thanks
Try this in C2 and drag down.
=IF(MONTH(A2)<>MONTH(A3),SUMPRODUCT((MONTH($A$2:$A$41)=MONTH($A2))*($B$2:$B$41)),"")
Last edited by Indi_Ra; 06-16-2014 at 06:30 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks