+ Reply to Thread
Results 1 to 7 of 7

SUMIF using the MONTH of the DATE in the criteria range

  1. #1
    Registered User
    Join Date
    07-24-2021
    Location
    Downingtown, PA, USA
    MS-Off Ver
    Office 365
    Posts
    2

    SUMIF using the MONTH of the DATE in the criteria range

    I have a table where one column is populated with dates and the next column is populated with numbers.

    I want to sum the numbers IF the MONTH of the DATE is the same as the MONTH of TODAY.

    For instance:
    A
    B
    1
    January 1 2
    2
    March 5 7
    3
    December 8 8
    4
    January 18 1
    5
    May 10 6

    Now, assume today is January 25. I want to sum the numbers where the month of the date is January (i.e. B1 + B4 = 3).

    I DO NOT want to create an additional column that performs the MONTH() function on the date column, and then use the month column as my SUMIF() criteria range.

    I DO want to somehow perform a function on the data from my criteria range, and then use the result of that function as my criteria.

    For instance, I was thinking something like this would work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Any help would be greatly appreciated.

    Thanks!
    Attached Files Attached Files
    Last edited by addisonian; 07-24-2021 at 12:38 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: SUMIF using the MONTH of the DATE in the criteria range

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: SUMIF using the MONTH of the DATE in the criteria range

    Use SUMPRODUCT as SUMIF will not work with MONTH and sample data did not have a July date!

    =SUMPRODUCT((MONTH(A1:A5)=MONTH(TODAY()))*(B1:B5))

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: SUMIF using the MONTH of the DATE in the criteria range

    Change at least one of the dates to July, then try this:

    =SUMPRODUCT((MONTH(A1:A5)=MONTH(TODAY()))*B1:B5)

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    01-Jan
    2
    2
    05-Mar
    7
    3
    08-Jul
    8
    4
    18-Jul
    1
    5
    10-May
    6
    6
    7
    8
    9
    <-- Doesn't work
    Sheet: Sheet1

  5. #5
    Registered User
    Join Date
    07-24-2021
    Location
    Downingtown, PA, USA
    MS-Off Ver
    Office 365
    Posts
    2

    Re: SUMIF using the MONTH of the DATE in the criteria range

    Ahh, thank you!

    So you can use the SUMPRODUCT() function to perform calculations on the criteria range. Basically, if your conditional statement is TRUE, then TRUE evaluates to 1. And 1 * x = x.

    Likewise, FALSE evaluates to 0. And 0 * x = 0.

    So then, when the sum of the products is calculated, you get the sum of those cells where your conditional evaluated to TRUE.

    Very nice!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: SUMIF using the MONTH of the DATE in the criteria range

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: SUMIF using the MONTH of the DATE in the criteria range

    Thanks for the rep.
    Last edited by AliGW; 07-24-2021 at 04:56 PM.

+ 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 Date in Range is a specific month
    By ExSkidder in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-07-2020, 05:19 PM
  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. [SOLVED] SUMIF but where Criteria is a Date Range
    By Scottpet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-28-2014, 08:41 AM
  5. [SOLVED] sumif formula for date and month with match criteria
    By alimamak in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-08-2012, 08:31 PM
  6. Excel 2008 : SUMIF using Name and Date Range criteria
    By cloudydaysong in forum Excel General
    Replies: 8
    Last Post: 10-19-2010, 09:06 PM
  7. SumIf using date criteria range and one other criteria
    By DJTMONEY in forum Excel General
    Replies: 1
    Last Post: 04-20-2010, 04:10 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