# Counting Tasks With Due Dates between TODAY and TODAY+7

1. ## Counting Tasks With Due Dates between TODAY and TODAY+7

Forgive my inexperience. I have a spreadsheet that lists several tasks with several different estimated completion dates having individual people as the task owners. I would like to have a formula in a cell that displays the total number of tasks due between today's date and a week from today's date that updates when each person filters on their own name as task owner.
ExcelHelp.xlsx
The number of rows varies in the tens or hundreds, so a solution that includes \$B:\$B would be helpful.

2. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Hello,

This formula might the what you are looking for
``Please Login or Register  to view this content.``
This will count the number of tasks (including duplicated) between TODAY() and a week after.

If somehow you want to sum the number on the task column however, it will be
``Please Login or Register  to view this content.``

3. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Hi and welcome to the forum

Take a look at the countifS() function for what you want, something like
=countifs(date_range,">="&today(),date_range,"<="&today()+7)
you can add additional croteria as needed

Also, not all members can open picture files (company fire walls and stuff), so please upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

4. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Thanks for the replies. Lem, your solution works fine except that the B range will vary on a daily basis; this is why I suggested using \$B:\$B. When I amend your formula using \$B:\$B, it works except when column C is filtered for a particular task owner, e.g. "David". The total remains at 8, rather than adjusting to the number of filtered cells. Does this make sense?
FDibbins, per your instructions, I will try to edit the original post to include a workbook sample rather than a picture.

5. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Originally Posted by FDibbins
=countifs(date_range,">="&today(),date_range,"<="&today()+7)
you can add additional croteria as needed
Thats why I added that last part. You can nclude an extra criteria for ,name_range,"David"

6. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

I can basically understand the concept of adding criteria as needed within the countifs function; however, I think I am needing something more along the lines of a SUMPRODUCT array formula? Something that has the bones of
Formula:
`Please Login or Register  to view this content.`
. I am wandering in over my head, because I don't understand arrays or how to adapt this formula to my needs.

7. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

In most cases, sumproduct can be replaced with formulas like sumifS() and countifS(), and this is 1 of those times.

=COUNTIFS(\$B:\$B,">"&TODAY(),\$B:\$B,"<="&TODAY()+7,\$C:\$C,"Tom")

"Tom" can be hard-coded like I did there, or you can put the name in a cell and reference it

8. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Try this...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(B2:B100>=TODAY()),--(B2:B100<=TODAY()+7))

9. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Originally Posted by Tony Valko
Try this...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(B2:B100>=TODAY()),--(B2:B100<=TODAY()+7))
This, essentially, is what I am after. Is there any way to do this without specifying an exact row range? The row numbers could be anywhere from 600-700; it changes daily. Would it just be best to put the row range at something like (B2:B5000) just to make sure?

10. ## Re: Counting Tasks With Due Dates between TODAY and TODAY+7

Just make the range big enough to include all the data.

If you expect the maximum number of rows of data to be ~700 then use B2:B800 as the range. You don't want to waste resources by having to calculate a boatload of empty cells!

##### Users Browsing this Thread

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