+ Reply to Thread
Results 1 to 6 of 6

Adding a date formula to an existing formula

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Bracknell
    MS-Off Ver
    Excel 2010
    Posts
    86

    Adding a date formula to an existing formula

    Hi Folks,

    I have an issue that is perplexing me.

    I currently have the below formula:
    =SUMPRODUCT(SUMIFS('Data - CURRENT'!$K:$K, 'Data - CURRENT'!$R:$R, Receipted_status, 'Data - CURRENT'!$B:$B, "Area 1"))
    This just pulls in all data regardless of the period.

    However, the data that I have is spanning a whole year. The data that I am reporting on is within a rolling 12 month period. I have two requests if I may be so bold:
    1. I need to just report on the previous month and bring back a certain total figure - hence the above formula.
    2. I need to report a breakdown of the previous 12 rolling months. This month report will be March 2013 to February 2014. Next month it will be April 2013 to March 2014 and so on.

    For the first one, to bring back the previous month I would use: =TEXT(DATE(2014,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM") but not sure if I am barking up the wrong tree and how to add this to my original formula??

    As I said...perplexing!

    Thanks for your help.

  2. #2
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Adding a date formula to an existing formula

    Can you upload a generic sample of your workbook.
    Have you thought about using pivot tables?

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Bracknell
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Adding a date formula to an existing formula

    Hi,

    Of course, my apologies for this. I have attached an example file. The dates should be based on column S. I am trying to stay away from Pivot Tables as this is only 1 formula of a multitude of formulas that work out different things.

    Hope that you can help.

    Andrew
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding a date formula to an existing formula

    nice formula

    Put this into sheet1 A3...
    =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

    Then adjust your formula to this...
    =SUMPRODUCT(SUMIFS('Data - CURRENT'!$I:$I, 'Data - CURRENT'!$P:$P,Receipted_status, 'Data - CURRENT'!$A:$A,$A5,'Data - CURRENT'!$S:$S,"<"&Sheet1!$A$3,'Data - CURRENT'!$S:$S,">="&EDATE(Sheet1!$A$3,-1)))

    Change the -1 (bold) to -12 for the 12-month requirement
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Bracknell
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Adding a date formula to an existing formula

    Hi Fdibbins

    I sincerely apologise - I completely missed your response and I feel so bad. Silly emails. Anyway - thank you so much...this works a treat.

    Much appreciated.
    Andrew

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Adding a date formula to an existing formula

    No problem, happy to help and thanks for the feedback

+ 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. adding to existing formula
    By wwoody in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2013, 03:25 AM
  2. Adding to 2 existing formula
    By cmackay1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2012, 05:14 PM
  3. [SOLVED] Adding a match formula to an existing formula
    By pauldaddyadams in forum Excel General
    Replies: 8
    Last Post: 08-16-2012, 05:14 PM
  4. [SOLVED] adding formula to existing database
    By Country Boy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11: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