+ Reply to Thread
Results 1 to 5 of 5

SUMIFS Based on Month

  1. #1
    Registered User
    Join Date
    12-14-2018
    Location
    Staffordshire, England
    MS-Off Ver
    365
    Posts
    60

    SUMIFS Based on Month

    Hi All,

    I have been trying to utilise SUMIFS to build a formula based on date range but seem to keep hitting brick walls.

    Per the test file attached I have a list of values for each month in the columns and the corresponding categories in the rows. I would like to add a YTD total but based on the a set month only so each month this can be updated therefore the total will only include months past such as Jan, Feb in Mar & Jan, Feb, Mar in Apr etc. I don't want to fix this value so would simply just prefer an option to enter it manually as the period to calculate to.

    Many thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,095

    Re: SUMIFS Based on Month

    if you change the values in A1 and C1 through N1 from the text of Jan and Feb to 1/1/2020 (but you can format as Jan) and in A1 you put the date then this sumproduct should help.
    =SUMPRODUCT(($C$1:$N$1<=$A$1)*($A$2:$A$7="category 2"),$C$2:$N$7)
    though category is hard coded in the formula, you can change it to point to a specific cell like A3 instead.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: SUMIFS Based on Month

    if you dont want to change the set up than you can use below in B2
    copy paste below in B2 then hold control and shift together then hit enter to make it array formula and then drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    12-14-2018
    Location
    Staffordshire, England
    MS-Off Ver
    365
    Posts
    60

    Re: SUMIFS Based on Month

    Many thanks both - all sorted and working!

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: SUMIFS Based on Month

    You are welcome !

+ 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. SUMIFS using Month
    By RadianWear in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2016, 09:23 PM
  2. [SOLVED] Using SUMIFS to count the number of inquiries per month based on the date of inquiry
    By Tryin2Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-25-2014, 04:11 AM
  3. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  4. SUMIFS used with month ?
    By spookiepower in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-29-2013, 06:44 AM
  5. Help With SUMIFS by Month
    By dsarkissian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2012, 08:48 PM
  6. Replies: 3
    Last Post: 01-19-2012, 08:53 AM
  7. Add values based on month using Sumifs or any other formula
    By ktmjamal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2012, 05:23 AM

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