+ Reply to Thread
Results 1 to 6 of 6

Calculating number of applications for the last week with dynamic range

  1. #1
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Calculating number of applications for the last week with dynamic range

    Hi all,

    I am working on a work project and we are trying to automate it as much as possible. So I came across this major problem.

    I have raw date with the date and number of applications submitted on each day. I used pivot table to count the number of applications by each day. It looks like this.

    date application
    1/1/13 -- 2
    1/2/13 -- 3
    2/5/13 -- 5
    Grand Total -- 10

    Now I want to calculate the number of applications within the week (back 7 days from today). I have tried different formulas but nothing makes sense to me. Can you guys help me out?

    Thanks.

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Calculating number of applications for the last week with dynamic range

    Assume your date start at A1, application at B1.Then next to GRAND TOTAL cell
    Exclude today
    Please Login or Register  to view this content.
    Include today
    Please Login or Register  to view this content.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Calculating number of applications for the last week with dynamic range

    Hi wenqq3, does this already take into account the dynamic range? I see that you set the range from 1 to 50; however, as the project goes on, more data will be included.

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculating number of applications for the last week with dynamic range

    In that case reference the entire columns

    First formula will become
    =SUMIFS(B:B,A:A,">="&(TODAY()-7),A:A,"<"&TODAY())
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Contributor
    Join Date
    08-22-2013
    Location
    US
    MS-Off Ver
    Mac Version 2011
    Posts
    126

    Re: Calculating number of applications for the last week with dynamic range

    Ace_XL, just to clarify, the pivot table has the grand total line below to sum all the applications up-to-date. I don't know how to make a dynamic range that excludes this grand total number.

    Note: pivot table is created using a macro, so let's assume we cannot move or change position of this row.

    Thank you!

  6. #6
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Calculating number of applications for the last week with dynamic range

    Hi huy_le, Ace_XL's suggestion can solve your problem.
    Or maybe you can upload a sample workbook without sensitive data. Thanks

    Upload Worbook
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

+ 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. calculating average against a group and week number
    By HiltonC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2013, 08:58 PM
  2. Calculating cycling week number.
    By bernmc in forum Excel General
    Replies: 2
    Last Post: 03-26-2010, 11:51 AM
  3. [SOLVED] Calculating the Week Number Corresponding with a Specific Date
    By Mindy in forum Tips and Tutorials
    Replies: 2
    Last Post: 08-23-2005, 11:54 AM
  4. [SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week
    By amy in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-23-2005, 11:42 AM
  5. Calculating The Week Number Corresponding With A Specific Date
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 07-24-2005, 01:49 AM

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