COUNTIF Problems!

[WBlack1234]

Hi there

I have a problem in that my COUNTIF formula keeps returning incorrect values.

I'm trying to create a table in my first worksheet which I want to count the number of enquiries that we have set to "Completed" in the last 7 days, but that are older than 7 days old ie- that their 'request received date' is more than 7 days old. I've copied my draft table below followed by the formula that I've created so far, I'd be grateful if someone could have a look and see where I'm going wrong!

All the other formulas used in the table work perfectly, but for some reason I just can't get the last one to work.

	CEF	E&E	SCS	CS	TS	CEO	ICT	OCS	PH	FOI Team	Total
New requests (received in last 7 days)
In Progress	3	12	7	1	3	6	1	0	1	2	36
Complete	0	0	0	0	0	0	0	0	0	0	0
Total New Requests	3	12	7	1	3	6	1	0	1	2	36
Other active requests (more than 7 days old)
In Progress Not Yet Due	5	9	8	2	0	11	2	1	1	1	40
In Progress Overdue	0	1	0	0	1	0	4	0	0	0	6
Completed in last 7 days (This is the problem row)	0	0	0	0	0	0	0	0	0	0	0
Total Requests in Progress	5	10	8	2	1	11	6	1	1	1	46
Total Requests	8	22	15	3	4	17	7	1	2	3	82

<tbody>
</tbody><colgroup><col><col span="11"></colgroup>

=COUNTIFS('Request Log'!$J:$J,Dashboard!D$8,'Request Log'!$A:$A,"<"&Q2-7,'Request Log'!$I:$I,"COMPLETE",'Request Log'!$C:$C,">="&Q2-7)

Many thanks

Wes

 

[oldbrewer]

where are the dates such as request date due date completed date

 

[WBlack1234]

Hi there, they are on the second worksheet ("Request Log"), not the one on which the formula is written.

I should probably mention that there is a control date, which the user can change in cell Q2 on sheet 1, hence the formula containing 'Q2-7', for example.

 

[oldbrewer]

please show us a few rows of request log

 

[WBlack1234]

please show us a few rows of request log

Here we are, I could also forward a copy of the excel doc if this helps? I can't see an attach file option on here however.

First working day of request	Date request must be completed	Date request actually completed	Legislation [FOI/EIR]	FOI Reference	Name of Applicant	Organisation	Information Requested	Status	Directorate handling request
04-Jul-14	31-Jul-14		FOI	1	Mr X	Organisation X	Information information information information information information.	In Progress	ICT
08-Jul-14	04-Aug-14		FOI	2	Mr X	Organisation X	Information information information information information information.	In Progress	TS
09-Jul-14	05-Aug-14	23-Aug	FOI	3	Mr X	Organisation X	Information information information information information information.	Complete	ICT
09-Jul-14	05-Aug-14	14-Aug	EIR	4	Mr X	Organisation X	Information information information information information information.	Complete	E&E
15-Jul-14	11-Aug-14	15-Jul	FOI	5	Mr X	Organisation X	Information information information information information information.	Complete	ICT
23-Jul-14	19-Aug-14		FOI	6	Mr X	Organisation X	Information information information information information information.	In Progress	OCS
24-Jul-14	20-Aug-14	27-Jul	FOI	7	Mr X	Organisation X	Information information information information information information.	Complete	ICT
24-Jul-14	20-Aug-14	29-Aug	FOI	8	Mr X	Organisation X	Information information information information information information.	Complete	ICT

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

sorry i am on holiday away for 5 days if not solved when i return i will try - have a cell with today's date in it and check if today minus completion date is less than 8 days