+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Using SUMIFS to calculate cells based on a specific month

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Using SUMIFS to calculate cells based on a specific month

    Hello Members,

    I am seeking assistance with the following formula.

    I wish to run a formula that totals the values in column B on "sheet 2" based on the month of a particular date in a cell on "sheet 1".

    For example if Cell A1 on Sheet 1 was the 13/01/2012, I want the formula to total all values in column B on sheet 2 where column A is the same month.

    A B
    6/01/2012 20.00
    07/01/2012 10.00
    13/01/2012 20.00
    13/02/2012
    14/03/2012

    I want this formula to read from the date cell on sheet 1, so this formula does not have to be manually updated.

    Any assistance with this is greatly appreciated.

    JASB

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Using SUMIFS to calculate cells based on a specific month

    You need SUMPRODUCT...

    =SUMPRODUCT(--(MONTH(A1:A500)=MONTH(Sheet1!A1)),B1:B500)

    Although, unless all of your dates are in the same year you probably want to expand it to:

    =SUMPRODUCT(--(MONTH(A1:A500)=MONTH(Sheet1!A1)),--(YEAR(A1:A500)=YEAR(Sheet1!A1)),B1:B500)

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,560

    Re: Using SUMIFS to calculate cells based on a specific month

    Hi,

    One way
    =SUMIFS(Sheet2!B:B,Sheet2!A:A,">="&EOMONTH(A1,-1)+1,Sheet2!A:A,"<="&EOMONTH(A1,0))
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    01-19-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using SUMIFS to calculate cells based on a specific month

    Andrew,

    Thanks for the prompt reply.

    This was exactly what I was after.

    Richard, thanks also.

    JASB

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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