+ Reply to Thread
Results 1 to 8 of 8

Auto Calculation Per Month

  1. #1
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    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
    Last edited by Smally; 06-16-2014 at 01:47 AM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    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
    Attached Files Attached Files
    Last edited by azumi; 06-16-2014 at 12:07 AM.

  3. #3
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Auto Calculation Per Month

    You can also use SUMPRODUCT function
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Array formulas can slow-down your worksheet a bit if your ranges are BIG.
    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

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,877

    Re: Auto Calculation Per Month

    How about trying a Pivot Table that groups by month?
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,583

    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

  6. #6
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: Auto Calculation Per Month

    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

  7. #7
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    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

  8. #8
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    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)),"")
    Last edited by Indi_Ra; 06-16-2014 at 06:30 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 05-17-2014, 10:18 PM
  2. [SOLVED] Auto instert Month names for This month, Last month and Next month
    By hemal89 in forum Word Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2013, 12:01 PM
  3. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2013, 08:45 AM
  4. [SOLVED] Help with a system to auto populate month names based on current month
    By rosboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2012, 05:17 PM
  5. [SOLVED] Auto populate cells from data in a 6 month range starting with the current month
    By ecarnley349 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:32 PM

Bookmarks

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