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
Training Stats

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Trainee[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Designation[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Infection control[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Infection control - Previous[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Safeguarding Adults[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Safeguarding Adults - Previous[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Basic Life Support[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Basic Life Support - Previous[/TD]
[TD="align: center"]Leaver[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]03/01/2003[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Bank[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]01/03/2003[/TD]
[TD="align: center"]MANAGER[/TD]
[TD="align: center"]20/12/18[/TD]
[TD="align: center"]20/12/18[/TD]
[TD="align: center"]15/12/18[/TD]
[TD="align: center"]20/12/18[/TD]
[TD="align: center"]23/12/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]01/09/2003[/TD]
[TD="align: center"]DOMESTIC[/TD]
[TD="align: center"]01/10/18[/TD]
[TD="align: center"]02/04/15[/TD]
[TD="align: center"]02/04/15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]01/08/2005[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]01/07/18[/TD]
[TD="align: center"]07/01/17[/TD]
[TD="align: center"]07/01/17[/TD]
[TD="align: center"]20/05/17[/TD]
[TD="align: center"]07/01/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Leaver[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]30/04/2006[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]29/12/18[/TD]
[TD="align: center"]29/12/18[/TD]
[TD="align: center"]29/12/18[/TD]
[TD="align: center"]21/10/18[/TD]
[TD="align: center"]21/10/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]04/06/2007[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]16/08/18[/TD]
[TD="align: center"]16/08/18[/TD]
[TD="align: center"]16/08/18[/TD]
[TD="align: center"]21/05/17[/TD]
[TD="align: center"]16/08/18[/TD]
[TD="align: center"]11/10/18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]06/11/2008[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]30/07/18[/TD]
[TD="align: center"]30/07/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/07/18[/TD]
[TD="align: center"]30/07/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]01/02/2009[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]15/072018[/TD]
[TD="align: center"]15/072018[/TD]
[TD="align: center"]15/07/18[/TD]
[TD="align: center"]19/09/17[/TD]
[TD="align: center"]20/04/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]13/02/2010[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]03/07/17[/TD]
[TD="align: center"]03/07/17[/TD]
[TD="align: center"]23/05/17[/TD]
[TD="align: center"]07/07/17[/TD]
[TD="align: center"]01/11/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Leaver[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]20/06/2010[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]25/05/18[/TD]
[TD="align: center"]25/05/18[/TD]
[TD="align: center"]25/05/18[/TD]
[TD="align: center"]25/05/18[/TD]
[TD="align: center"]25/05/18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]28/06/2010[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]04/11/17[/TD]
[TD="align: center"]03/12/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]07/10/2010[/TD]
[TD="align: center"]D.MANAGER[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]12/10/18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]26/10/2010[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]23/11/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]09/05/2011[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]14/12/18[/TD]
[TD="align: center"]14/12/18[/TD]
[TD="align: center"]31/10/18[/TD]
[TD="align: center"]18/10/18[/TD]
[TD="align: center"]09/12/16[/TD]
[TD="align: center"]11/10/18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]31/10/2011[/TD]
[TD="align: center"]ADMIN[/TD]
[TD="align: center"]08/09/15[/TD]
[TD="align: center"]08/09/15[/TD]
[TD="align: center"]08/09/15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Leaver[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Infection Control[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Safeguarding Adults[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Basic Life Support[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Level 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Level 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: center"]Level 3[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]DOMESTIC[/TD]
[TD="align: center"]MANAGER[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]ADMIN[/TD]
[TD="align: center"]D. MANAGER[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]SHSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Stat. & Mand. Training​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D20[/TH]
[TD="align: left"]=Count_Training(Tier1[[Infection control]:[Infection control - Previous]],TODAY(),Tier1[Leaver])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E20[/TH]
[TD="align: left"]=Count_Training(Tier1[[Safeguarding Adults]:[Safeguarding Adults - Previous]],TODAY(),Tier1[Leaver])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F20[/TH]
[TD="align: left"]=Count_Training(Tier1[[Basic Life Support]:[Basic Life Support - Previous]],TODAY(),Tier1[Leaver])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]