# Help?! Need to create formula for date-range counter

#### Illusion

##### New Member
Hi all, I'm new to the forum, and have been an Excel user for about 2 years. I'm using Office 2003 on the PC (Windows XP SP2)

I've recently created a spreadsheet to track DVD rentals from online DVD mail-order companies. I've been able to develop formulas and macros to keep track of what DVDs I've reserved, which ones have been sent, returned by me, and then received back by the online company. Using this info I'm able to determine how many DVDs are in transit (coming or going) and what the turnaround time was (in days) between the time the DVD was sent and when the company reported it returned.

What I haven't been able to figure out is how to automatically tally the quantity of DVDs I was able to rent in a billing period (typically 30 days).

Here's how I have things set up:

I have "Worksheet 1" that keeps track of all the DVDs I've reserved/received, from which company, and dates sent/received, etc. I've created macros that allow me to sort the data by any column (Title, Company, Date Sent, Date Arrived, Date I Returned, Date Confirmed Returned by company).

I have "Worksheet 2" that I use to track my billing. It contains a "Start Date" and an "End Date" for the billing period, as well as the billing amount.

Using this data, combined with information from "Worksheet 1", I'm able to keep tabs on how much it's costing to rent a DVD (on average, based on total values), but I'd also like to be able to determine how many DVDs each company has sent me within the billing period in order to gauge mail efficiency, turnaround time, best bang for the buck, etc.

Is it possible to extract a tally of how many DVDs were sent by a company between two dates?

As a working example:

My billing period - August 8, 2005 (column A) to September 8, 2005 (column B)

Goal is to tally a 'counter' in a cell at the end of each row on my billing worksheet, which is based on a formula which looks at TWO columns on "Worksheet 1" (DVD Company and Date Sent) and compares this information to the company and date range in the two columns of the billing "Worksheet 2".

If a DVD was sent out by a company on August 10, 2005 based on data from Worksheet 1 (falling within the date range between cell A & cell B on a row in Worksheet 2) by company "X" (matching the billing company in cell C in Worksheet 2) then the counter (in a cell at the end of the same row on Worksheet 2) would be incremented by one. The formula would then continue to look for all other date matches within the date range AND company matches determined by the Start/End dates of the billing period as well as the DVD company matching the designated billing data; if a match was found then the number would continue to be incremented.

The end result I'm looking for is to have a dynamically created tally of how many DVDs I was able to rent monthly from each company, derived by the "Sent Dates" relation to the date range of the billing period.

If I can figure out a way to do this 'experimental' project then I also have a use for it on another project I have in mind for monthly customer job-tracking for my business purposes, which would prove to be a much more valuable and worthwhile task.

I've exhausted every avenue in trying to resolve this predicament, and maybe it's not possible... or perhaps it's so simple that I've overlooked the obvious?!

All help will be greatly appreciated.

Illusion

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Barrie Davidson

##### MrExcel MVP
Would a SUMPRODUCT work for you? Assuming:
• Start date in cell A1
• End date in cell B1
• Company in cell C1
• Data on worksheet one is 'Sheet1'!A1:B3000

This formula should do the trick:

=SUMPRODUCT(('Sheet1'!\$A\$1:\$A\$3000>=A1)+0,('Sheet1'!\$A\$1:\$A\$3000<=B1)+0,('Sheet1'!\$B\$1:\$B\$3000=C1)+0)

[EDIT: Welcome to MrExcel!]

Regards,

#### Illusion

##### New Member
Excellent! Thanks so much -- it works like a charm! It's obvious that I'm going to have to explore the SUMPRODUCT formula some more.

I really appreciate your very quick response and embarasslingly simple solution to my problem.

Illusion

Replies
0
Views
115
Replies
12
Views
305
Replies
4
Views
331
Replies
1
Views
176
Replies
5
Views
234

1,195,833
Messages
6,011,853
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back