# Thread: Count within a date range (Excel 2000)

1. ## Count within a date range (Excel 2000)

Hello,

This seems like it should be so simple, but I am in need of assistance. I need to be able to count the number of times a group performs a step within a particular date range. The date range is a variable, typed in at the top of the page by a user.

The attached spreadsheet provides a small sample of the data I am working with. The blue dates are what the user will enter, and will change with each use. The yellow cells are where I need to show how many times a Board opened a file, and how many times they closed a file - within the particular date range.

I appreciate any guidance!

2. ## Re: Count within a date range (Excel 2000)

In cell F3, enter this formula:

=SUM((\$B\$3:\$B\$14=\$E3)*(\$A\$3:\$A\$14>=\$B\$1)*(\$A\$3:\$A\$ 14<=\$C\$1))

It is an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. In cell G3, enter this formula:

=SUM((\$B\$3:\$B\$14=\$E3)*(\$C\$3:\$C\$14>=\$B\$1)*(\$C\$3:\$C\$ 14<=\$C\$1))

also as an array formula (Ctrl+Shift+Enter). Then, select F3:G3 and fill down to row 5. You can make the formulas more readable by defining and using named ranges; see attached version. You'd get formulas such as

=SUM((Responsible_Board=\$E3)*(Open_Date>=Start_Dat e)*(Open_Date<=End_Date))

3. ## Re: Count within a date range (Excel 2000)

Thank you so much, Hans.

That works perfectly!
Mary

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•