+ Reply to Thread
Results 1 to 10 of 10

SUMIF Date in Range is a specific month

  1. #1
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    SUMIF Date in Range is a specific month

    This is a dynamic worksheet with new rows added daily throughout the year. I want to get monthly totals from the data so I can create a line chart on the monthly totals.

    Column A is DATE
    Column B is Amount

    In cell E2 I want to SUMIF the month in the date range is 1
    In cell F2 I want to SUMIF the month in the date range is 2
    In cell G2...SUMIF month...is 3

    and so on

    I tried variations of =SUMIF(A:A,MONTH(A:A)="1",B:B) and =SUMIF(MONTH(A:A),"1",B:B)

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: SUMIF Date in Range is a specific month

    Try in E2 copied to the right >> =SUMPRODUCT(--(TEXT($A$2:$A$12,"mmmm")=E1),$B$2:$B$12)

    Since you said you will be adding row, then we can make this dynamic.

    Create two named ranges
    myAmount =OFFSET(Sheet1!$B$2,,,COUNT(Sheet1!$B:$B))
    myDate =OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A:$A))

    Then in E2 copied to the right >> =SUMPRODUCT((TEXT(myDate,"mmmm")=E1)*(myAmount))
    Last edited by jeffreybrown; 02-04-2020 at 07:30 PM.
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,591

    Re: SUMIF Date in Range is a specific month

    Another way.

    In E2 and filled across
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  4. #4
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: SUMIF Date in Range is a specific month

    jeffreybrown,

    The first formula worked fine. Of course the range is limited to the first 12 rows.
    The second formula is producing very large numbers, instead of 250 January equals 43,475. So something is off with it. I tried analyzing the formula and noticed the second formula didn't contain "--" in the first argument. Adding them changed the result to "0". Likewise, the first formula contains an array1 and array2, whereas the second formula has an asterisk "*" instead of a comma ",". Switching those out also produced a "0". I've uploaded the sample file for convenience.
    Attached Files Attached Files
    Last edited by jeffreybrown; 02-07-2020 at 04:08 PM. Reason: Please don't quote full posts!

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    633

    Re: SUMIF Date in Range is a specific month

    E2=if(e$1<>"",sumproduct((month($a$2:$a$100)=month(e$1&0))*($a$2:$a$100<>"")*($b$2:$b$100)),"")


    Copy across

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: SUMIF Date in Range is a specific month

    Hi ExSkidder,

    Somehow the myDate and myAmount got turn around on the column references.

    In the Name Manager, Swap A and B and it works fine.

    myAmount =OFFSET(Sheet1!$B$2,,,COUNT(Sheet1!$B:$B))
    myDate =OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A:$A))
    Last edited by jeffreybrown; 02-07-2020 at 04:07 PM.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: SUMIF Date in Range is a specific month

    In the first formula, I used what's call a double unary. It's just a method to coerce TRUE/FALSE to 1/0 so calculation can be made.

    In the second formula I used the * which is just multiplying the two arrays.

    To really investigate what is happening with a formula...

    Evaluate Formula
    • Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

  8. #8
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: SUMIF Date in Range is a specific month

    Quote Originally Posted by jeffreybrown View Post
    In the first formula, I used what's call a double unary. It's just a method to coerce TRUE/FALSE to 1/0 so calculation can be made.

    In the second formula I used the * which is just multiplying the two arrays.

    To really investigate what is happening with a formula...

    Evaluate Formula
    • Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula
    Thank you for the additional instruction. It worked beautifully!

  9. #9
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: SUMIF Date in Range is a specific month

    Quote Originally Posted by FlameRetired View Post
    Another way.

    In E2 and filled across
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This was a great option also! Thank you FlameRetired.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,591

    Re: SUMIF Date in Range is a specific month

    You are welcome. Glad to help. Thank you for the feedback and added rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] SUMIF with Specific date & name in next sheet with month name
    By nandhavnk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2019, 05:32 AM
  2. Replies: 12
    Last Post: 04-19-2019, 05:23 PM
  3. STUCK** SUMIF INDEX MATCH - formula output sum of date range or month sum
    By Antprod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2015, 08:13 AM
  4. Replies: 9
    Last Post: 10-08-2015, 01:50 PM
  5. Does a task start or finish date fall within 3 month range of a specific date
    By jamesmcgallan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2015, 07:46 AM
  6. Replies: 6
    Last Post: 09-20-2013, 04:17 PM

Tags for this Thread

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