Formula Problem with count under conditions within date range

**txt_mess** · 08-24-2010, 06:35 AM

Hi guys

I am trying to work out a formula but need some help

In a sheet called Closed Requests I have data like this

Item Number Recieved Date Actual Delivery Date Due Date Type
0030 25 May 2010 01 June 2010 13 June 2010 HC
0032 27 May 2010 01 June 2010 05 June 2010 HC
0034 27 May 2010 01 June 2010 30 May 2010 CR
0035 28 May 2010 02 June 2010 07 June 2010 HC
0041 02 June 2010 02 June 2010 03 June 2010 CR
0039 01 June 2010 03 June 2010 11 June 2010 HC
0036 31 May 2010 04 June 2010 15 July 2010 HC
0027 17 May 2010 09 June 2010 13 June 2010 CR
0040 02 June 2010 10 June 2010 13 June 2010 HC
0029 25 May 2010 11 June 2010 13 June 2010 HC
0046 07 June 2010 16 June 2010 07 July 2010 CR
0047 08 June 2010 16 June 2010 07 July 2010 CR

And on my calcutaion sheet title Weekly Figures I have

Start Date End Date
10/05/2010 16/05/2010
17/05/2010 23/05/2010
24/05/2010 30/05/2010

What I am looking to do is count the number of CR, HC , PR requests between a particular date range specified on the other sheet.

I can get a general count ok by using

=SUMPRODUCT(('Closed Requests'!B16:'Closed Requests'!B238>B18)*('Closed Requests'!B16:'Closed Requests'!B238<C18))

but I cannot seem to get the same count with an arguement that the value must equal CR, HC , PR .

Any ideas or am I just completely off ?

Thanks,
txt_mess

**royUK** · 08-24-2010, 06:51 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I would think a PivotTable would be the best way