+ Reply to Thread
Results 1 to 6 of 6

Changing the cell locations of a forumla based on Date

Hybrid View

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Changing the cell locations of a forumla based on Date

    Here's what I'm trying to do.

    I have rows of data that have a date associated with them.

    I want to be able to enter a date range in my spreadsheet and for a cell with a formula to determine based on those dates which cells to use in its calculations.

    The way I do it now is to manually look at the data. I see that for example Feb 1st through Feb 15th is in cells B100 through B155. Then I manually change the formula to look at cells B100 through B155. The next day I have to change the formula again to look at 2/2 through 2/16 etc...

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing the cell locations of a forumla based on Date

    Post a sample workbook showing the actual sample data layout, then examples of TWO formulas you create from that data, we'll see if we can give you ONE that does what both formulas do on it's own.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    02-26-2013
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Changing the cell locations of a forumla based on Date

    I can't cut and paste my spreadsheet, but I'll explain the issue better.

    I have 2 columns. One has a date and another has a value. I want to be able to enter a date range. Let's say
    01/02/2013 - 01/03/2013. I want a cell that will know to add the sum of B4 through B8 that correspond to those dates.The 4 and 8 in this case have to be a kind of variable value of the B column that will change depending which date
    range I want.

    A B

    1 01/01/2013 10
    2 01/01/2013 15
    3 01/01/2013 17
    4 01/02/2013 09
    5 01/02/2013 05
    6 01/02/2013 06
    7 01/02/2013 15
    8 01/03/2013 08
    9 01/04/2013 20
    1 01/04/2013 25

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing the cell locations of a forumla based on Date

    The function you want is SUMIFS().


    =SUMIFS(B:B, A:A, ">=01/02/2013", A:A, "<=01/03/2013")

  5. #5
    Registered User
    Join Date
    02-26-2013
    Location
    NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Smile Re: Changing the cell locations of a forumla based on Date

    Thank you.

    That did the trick. It worked so well, I may be back with another question.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing the cell locations of a forumla based on Date

    If that takes care of your original query for this thread, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ 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