+ Reply to Thread
Results 1 to 9 of 9

Trying to sum YTD actuals using sumif by using names of months as the range and not dates

  1. #1
    Registered User
    Join Date
    08-11-2013
    Location
    washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Trying to sum YTD actuals using sumif by using names of months as the range and not dates

    Hello,

    I have a spreadsheet that has a list of months in one column (Column A) and actuals and projections in another column (Column B) and I am calculating the YTD actuals at the end of each month. The fiscal year begins in October so I have actuals through January.

    I have used the sumif formula to calculate a YTD total of the actuals by changing the names of the months to dates and then formatting as "MMMM" and using the Today() function : =SUMIF($A$6:$A$17,"<="&TODAY(),B6:B17)

    Is it possible to calculate the YTD actuals without converting the names of the months to dates?

    Thank you in advance for your help.


    Attachment 562726
    Last edited by randiesq; 02-24-2018 at 06:14 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    08-11-2013
    Location
    washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    Hello,

    I have attached the file. The first tab calculates the YTD total using the basic sum function in cells B14 to I14 for each category but I have to adjust the sum formula each month after I receive new actuals. The second tab uses the SUMIF function based on dates.

    Is it possible to use a sumif function without changing the names of the months to dates?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    What is wrong with having months as dates and formatted as "mmmm" ? This is best solution: you should always use Excel dates rather than text..

    If you use months as TEXT how are you going to manage the change of year (vs TODAY() test) ?

  5. #5
    Registered User
    Join Date
    08-11-2013
    Location
    washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    Thank you. I agree with you completely. I was trying to see if there was another way because I will need to pass the spreadsheet on to co-workers who are not good with Excel but will need to use the spreadsheet and alter it for the next fiscal year. I wanted to automate the file as much as possible so that they would not have to change the dates but I appreciate the answer.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    This looks like it might be idiot-proof.

    With the first year of the fiscal year in B1, use this in A4, copied down. It's formatted as date at the moment, but you'll want to format it as "mmmm".

    =DATE(IF(1+MOD(8+ROWS($1:1),12)<10,$B$1+1,$B$1),2+MOD(8+ROWS($1:1),12),0)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    in A4 and copied down (based on Glenn's helper in B1)

    =EOMONTH(DATE($B$1,ROWS($1:10),1),0)

    EDIT:updated formula
    Last edited by JohnTopley; 02-25-2018 at 04:15 AM.

  8. #8
    Registered User
    Join Date
    08-11-2013
    Location
    washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Smile Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    Thank you both very much. This works perfectly.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Trying to sum YTD actuals using sumif by using names of months as the range and not da

    Thanks JT. Nice !!

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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 for Dates / Months ?
    By Rowan in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 05:05 PM
  2. [SOLVED] SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  3. SUMIF for Dates / Months ?
    By Rowan in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 09:05 AM
  4. [SOLVED] SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. [SOLVED] SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. SUMIF for Dates / Months ?
    By ZMAN in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 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