# Calculating number of applications for the last week with dynamic range

1. ## 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. ## 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.``

3. ## 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. ## 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())

5. ## 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. ## 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

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.

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

#### 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