Count - VBA Help

[janarthenan]

Hi All

I currently have the below UDF for my spreadsheet.
It looks at two columns and counts the highest date, if the leavers column is empty and the date entered is less than the DateToCheck

I would like to add 2 more variables to this UDF if possible.

1. I would the UDF to count based on Designation which is on a seperate level table.
2. I would like the UDF to ignore row if the StartDate is after DateToCheck.

Also currently the Previous and the Current columns are next to each other, THe UDF does not work if the columns are not next to each other.
I would like to keep the Previous columns together.

Function Count_Training(RngToLookIn As Range, DateToCheck As Date, rngIgnore As Range) As Long  Dim aRangeToLookIn As Variant, aIgnore As Variant
  Dim EarlierDate As Date
  Dim i As Long, j As Long
  
  aRangeToLookIn = RngToLookIn.Value
  aIgnore = Intersect(RngToLookIn.EntireRow, rngIgnore.EntireColumn).Value
    EarlierDate = DateAdd("m", -12, DateToCheck)
  For i = 1 To UBound(aRangeToLookIn, 1)
    If Len(aIgnore(i, 1)) = 0 Then
      For j = 1 To UBound(aRangeToLookIn, 2)
        If aRangeToLookIn(i, j) <= DateToCheck Then
          If aRangeToLookIn(i, j) >= EarlierDate Then
            Count_Training = Count_Training + 1
            Exit For
          End If
        End If
      Next j
    End If
  Next i
End Function

	A	B	C	D	E	F	G	H	I	J
1	Trainee	Start Date	Designation	Infection control	Infection control - Previous	Safeguarding Adults	Safeguarding Adults - Previous	Basic Life Support	Basic Life Support - Previous	Leaver
2	A	03/01/2003	HSCW							Bank
3	B	01/03/2003	MANAGER	20/12/18	20/12/18	15/12/18	20/12/18	23/12/18
4	C	01/09/2003	DOMESTIC	01/10/18	02/04/15	02/04/15
5	D	01/08/2005	SN	01/07/18	07/01/17	07/01/17	20/05/17	07/01/17		Leaver
6	E	30/04/2006	SN	29/12/18	29/12/18	29/12/18	21/10/18	21/10/18
7	F	04/06/2007	SHSC	16/08/18	16/08/18	16/08/18	21/05/17	16/08/18	11/10/18
8	G	06/11/2008	SN	30/07/18	30/07/18		30/07/18	30/07/18
9	H	01/02/2009	SN	15/072018	15/072018	15/07/18	19/09/17	20/04/17
10	I	13/02/2010	SHSC	03/07/17	03/07/17	23/05/17	07/07/17	01/11/16		Leaver
11	J	20/06/2010	SN	25/05/18	25/05/18	25/05/18	25/05/18	25/05/18
12	K	28/06/2010	SHSC	15/08/18	15/08/18	15/08/18	04/11/17	03/12/17
13	L	07/10/2010	D.MANAGER	31/10/18	31/10/18	31/10/18	31/10/18	31/10/18	12/10/18
14	M	26/10/2010	SHSC	15/08/18	15/08/18	15/08/18	15/08/18	23/11/16
15	N	09/05/2011	SHSC	14/12/18	14/12/18	31/10/18	18/10/18	09/12/16	11/10/18
16	O	31/10/2011	ADMIN	08/09/15	08/09/15	08/09/15				Leaver
17
18
19				Infection Control	Safeguarding Adults	Basic Life Support		Level 1	Level 2	Level 3
20			Training Stats	10	10	7		HSCW	DOMESTIC	MANAGER
21								SN	ADMIN	D. MANAGER
22								SHSCW

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Stat. & Mand. Training​

Worksheet Formulas Cell Formula D20 =Count_Training(Tier1[[Infection control]:[Infection control - Previous]],TODAY(),Tier1[Leaver]) E20 =Count_Training(Tier1[[Safeguarding Adults]:[Safeguarding Adults - Previous]],TODAY(),Tier1[Leaver]) F20 =Count_Training(Tier1[[Basic Life Support]:[Basic Life Support - Previous]],TODAY(),Tier1[Leaver]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>