How to COUNT the number of times a range of info shows up within another range of info

[emilysbs]

I need to figure out a formula to count the number of times a range of data shows up in another range of data. Basically, I have 2 columns of data. One column is date entires and the other is a corresponding value to that date. There are also many entries for each date (i.e. 1/1/2011 could have been entered 50 times, but each entry would have a different value). A data entry center is being monitored by how long it takes them to complete 1 data entry. So each time they compelte one, the date it was completed and the length of time it took them to complete is entered into 1 row of data. I need to first sort the data by weeks (i.e. all entries made between 1/1/2011 and 1/7/2011) and then sort that information into 4 categories (entries that took between 0 and 3 minuted, 3 and 6 minutes, 6 and 9 minutes, and longer than 9 minutes) and how many of those entries there are for each of the 4 groups. So, for example, maybe on Week 1, there were 35 entries that had a value between 0 and 3 minutes. Now, I can sit here and do this manually, using the Filter and sort features... but I want to know if there is a formula I can use that will COUNT the number of entries for me.

[mrice]

I suggest that you look at the WEEKNUM function to identify dates in the same week and the COUNTIF function to help with the counting.

If you post an actual example, it will be much easier to help.

[emilysbs]

I tried to upload the excel file, but it didn't work for some reason.

Here is an example data set:

[Column 1]

Date Created
1/1/2011
1/1/2011
1/1/2011
1/2/2011
1/2/2011
1/2/2011
1/3/2011
1/3/2011
1/4/2011
1/5/2011
1/5/2011
1/6/2011
1/7/2011
1/7/2011
1/8/2011
1/9/2011
...etc...

[Column 2]

Response Time
330
280
169
1141
1109
979
612
450
350
550
77
32
472
519
636
564

So if you put that info into two columns side by side, that is what I am looking at. Currently I put filters on the the first row (the title row) and I choose "Date Filters" from the little drop down arrow and then I choose "between". I enter the dates I want to filter for (1/1/2011 to 1/7/2011) so that I it sorts it so that I only look at the data that is in that week (hundreds of entries). Then I filter the second column by selecting "numbers filter" and then between. First I sort the data by a data set of 0-180, I highlight all the results to get the "count" and then enter that number into a separate excel file that I'm using to keep track of my results. I do this many times over trying to find how many entries were made on such and such week that fall into each of 4 ranges (0-180, 181-360, 361-540, and 540-999999). So for each week, I end up with 4 different numbers as my final result.

Hopefully that explains it a little better?

[mrice]

Please have a look at the attachment.

You will see that I have used the WEEKNUM function to get a numberic value for the week in column C and a COUNTIFS function with a mixture or relative and absolute addressing in columns G to J. The latter reference the upper and lower bounds of your bands in G1 to J2.

The data that I have in there is taken from your post but may look different as my machine is set up with the European date format in which month and day are reversed.