Count out of date record

[JoePr]

I have a tracker for training and want to count how many records are out of date based on team and whether or not they are active. Column B says if Leaver, Column D has the team and column E - H have the records.

If column B is Blank (active record) then how many records in Column E are more than 11 months old (ie ready for annual refresher) then group the answers by team. a sample of the info is below, I couldn't attach an excel sample. I am creating tables and charts on a second tab.

Hope someone can help

Leaver (X)	Name	Team	Info	Bias	H&S	Basic Fire Awareness	Fraud	Business Continuity
		AEA	08/03/2019	25/02/2019	08/03/2019	08/03/2019	08/03/2019
x		AEA
		AEA	18/09/2017
x		BSP	03/05/2019	03/05/2019	03/05/2019	03/05/2019	03/05/2019
		BSP	08/03/2019	08/03/2019	08/03/2019	01/07/2014	16/08/2013
		BSP	01/08/2018	01/01/2015		01/01/2015	01/01/2015
		BSP
		PMO	25/02/2019	13/02/2014	25/02/2019	26/02/2019	01/02/2013
		PMO	25/02/2019	16/01/2018	25/02/2019	17/01/2018	16/01/2018
		PMO	28/03/2019	08/03/2019	08/03/2019	08/03/2019	08/03/2019
x		PMO	19/02/2019	06/12/2017	19/02/2019	06/12/2017	19/02/2019
		PMO	21/09/2019	10/07/1905	21/09/2019	21/09/2019	21/09/2019
		PMO
x		PMO
		PMO
		VH
x		VH
		VH
		VH
x		VH
		VH

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

Joe

 

[ChrisFoster]

Put the the following formula into any free column on row 2, this will present either a 1 or a 0. If it's a 1 then it meets the criteria of B2 being blank and E2 being more than 11 months old, if its a 0 then it doesn't meet that criteria.

=COUNTIFS(B2,"",E2,"<="&TODAY()-334)

Does this help?

 

[Fluff]

How about

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Leaver (X)	Name	Team	Info	Bias	H&S	Basic Fire Awareness	Fraud	Business Continuity			Info	Bias	H&S	Basic Fire Awareness	Fraud
2			AEA	08/03/2019	25/02/2019	08/03/2019	08/03/2019	08/03/2019			AEA	1	0	0	0	0
3	x		AEA								BSP	1	1	0	2	2
4			AEA	18/09/2017							PMO	0	3	0	1	2
5	x		BSP	03/05/2019	03/05/2019	03/05/2019	03/05/2019	03/05/2019			VH	0	0	0	0	0
6			BSP	08/03/2019	08/03/2019	08/03/2019	01/07/2014	16/08/2013
7			BSP	01/08/2018	01/01/2015		01/01/2015	01/01/2015
8			BSP
9			PMO	25/02/2019	13/02/2014	25/02/2019	26/02/2019	01/02/2013
10			PMO	25/02/2019	16/01/2018	25/02/2019	17/01/2018	16/01/2018
11			PMO	28/03/2019	08/03/2019	08/03/2019	08/03/2019	08/03/2019
12	x		PMO	19/02/2019	06/12/2017	19/02/2019	06/12/2017	19/02/2019
13			PMO	21/09/2019	10/07/1905	21/09/2019	21/09/2019	21/09/2019
14			PMO
15	x		PMO
16			PMO
17			VH
18	x		VH
19			VH
20			VH
21	x		VH
22			VH
Sheet4

Cell Formulas
Range	Formula
M2	=COUNTIFS($B$2:$B$22,"<>x",$D$2:$D$22,$L2,E$2:E$22,"<"&EDATE(TODAY(),-11))

Copy formula down & across

 

[JoePr]

Hi, thanks for your response, and yes this works perfectly, but I need to know the results by team, so how many in AEA (1), BSP (1) so need to add another criteria which is the team in column D. I have tried this, but get a "#VALUE!" error

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIFS(Tracker!B2:B500,"",Tracker!E2:E500,"<="&TODAY()-334,Tracker!D3:D500,"AEA")[/FONT]

 

[JoePr]

Thank you, working great.

 

[Fluff]

Glad we could help & thanks for the feedback