+ Reply to Thread
Results 1 to 5 of 5

Last 180 Days Formula

  1. #1
    Registered User
    Join Date
    06-16-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Last 180 Days Formula

    Hello,

    I am having trouble creating a formula that will find rows that have a date in the last 180 days, and then use the sum of a specific column in those rows.

    ie:


    Date Total

    June 3, 2013 4 <---- Use This Data
    April 4, 2013 4 <---- Use This Data
    April 9, 2013 1 <---- Use This Data
    January 7, 2013 7 x-----Do Not Use This Number

    Sum
    9

    Thanks

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Last 180 Days Formula

    Why would January 7, 2013 be excluded? It's within today minus 180 days.

    Today = 6/16/2013

    6/16/2013 - 180 days = 12/18/2012
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Last 180 Days Formula

    Maybe you want:

    180 days prior to current date
    =SUMIF(A1:A20,">="&TODAY()-180,B1:B20)

    180 days prior to most recent date
    =SUMIF(A1:A20,">="&MAX(A1:A20)-180,B1:B20)
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    06-16-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Last 180 Days Formula

    Quote Originally Posted by Tony Valko View Post
    Why would January 7, 2013 be excluded? It's within today minus 180 days.

    Today = 6/16/2013

    6/16/2013 - 180 days = 12/18/2012
    Oops! Been a long day, should be January 7, 2012!

    Thanks, that looks like what I'm looking for!

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Last 180 Days Formula

    OK, then this should do what you want.

    =SUMIF(A2:A5,">="&TODAY()-180,B2:B5)

    Adjust the ranges to suit.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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