Countif only one per row

J

janarthenan

New Member
Joined
Jun 11, 2016
Messages
22
Hi All

I have been trying to achieve the following for days now and can't seem to figure out a way.

I have a spreadsheet with dates when a supervision is done for a particular staff.

I am trying to have a weekly report so that I know on that particular week what my supervision Percentage was.

Every staff needs their supervision done at least every 3 months.

When I do the COUNTIF and if some one had their supervision twice (one in April and one in June) on the June calculation it counts it as 2.

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Name[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Sept[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Last Supervision[/TD]
[TD]Next Supervision[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/4/17[/TD]
[TD][/TD]
[TD]1/6/17[/TD]
[TD]1/7/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=MAX(B2:J2)[/TD]
[TD]=H2+90[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]1/5/17[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/8/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=MAX(B3:J3)[/TD]
[TD]=H3+90[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I do COUNTIF(B2:J2, "<="&1/4/17) - I get 1
When I do COUNTIF(B2:J2, "<="&1/7/17) - I get 3 (Which is correct) but I want this to be one.
- So basically I want the calculation to count the highest value only per row and If that highest value meets the COUNTIF condition to come back as 1.

Prefer if no VBA was used.

Regards
J
 
Sort by date Sort by votes
Hi Peter

Your creation has not failed me, but i was wondering if you can help me with one last thing.

I would like the formula to Omit a row if "0" is present in that column.

Is this possible?

Jan
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
janarthenan said:
I would like the formula to Omit a row if "0" is present in that column.
Click to expand...
What column?

This is not fresh in my mind, so can you post another small set of sample data, expected result(s) and explanation of this new requirement in relation to that sample data?
 
Upvote 0
Hi Peter

This was the code you originally created for me:

Code: 
Function Count_Rows_With_Ignore(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("d", -90, DateToCheck)
  'EarlierDate = DateAdd("m", -3, DateToCheck)
  For i = 1 To UBound(aRangeToLookIn, 1)
    If IsEmpty(aIgnore(i, 1)) Then
      For j = 1 To UBound(aRangeToLookIn, 2)
        If aRangeToLookIn(i, j) <= DateToCheck Then
          If aRangeToLookIn(i, j) >= EarlierDate Then
            Count_Rows_With_Ignore = Count_Rows_With_Ignore + 1
            Exit For
          End If
        End If
      Next j
    End If
  Next i
End Function

It looks at a range of data and counts the number of rows that have a date within the last 90 days (3 months).
If there are two dates in a row, it counts the newest date.
It also ignores a row, if a value is entered in a "Leaver" Column.

Now I would like to add another column "Years of Service" and if the value is 0 here to ignore these rows too.

BCDEFGHI
105/06/2017
203/10/2016
304/08/2017
402/05/2017
501/08/2014
606/11/2017
714/08/2017
823/10/2017
921/02/2017
1014/11/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]06/06/2017[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1[/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"]4[/TD]

[TD="align: center"]0[/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"]5[/TD]

[TD="align: center"]0[/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"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]SHSC[/TD]
[TD="align: center"]01/04/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]26/07/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]0[/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"]8[/TD]

[TD="align: center"]0[/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"]9[/TD]

[TD="align: center"]0[/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"]10[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]COOK[/TD]
[TD="align: center"]07/04/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"][/TD]
[TD="align: center"]05/05/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Appraisals​
Column D is the years of service and Row 1 contains a date in H, the count should ignore this as column D contains a 0.

To explain - These are people who are due appraisals, sometimes the appraisals are completed way before they are due and I want these to be ignored.

Hope this explains it.

Thank You Once again

Jan
 
Upvote 0
OK, try this
Code: 
Function Count_Rows_With_2Ignores(RngToLookIn As Range, DateToCheck As Date, rngIgnoreLeavers As Range, rngIgnoreYears As Range) As Long
  Dim aRangeToLookIn As Variant, aIgnoreLeaver As Variant, aIgnoreYears As Variant
  Dim EarlierDate As Date
  Dim i As Long, j As Long
  
  aRangeToLookIn = RngToLookIn.Value
  aIgnoreLeaver = Intersect(RngToLookIn.EntireRow, rngIgnoreLeavers.EntireColumn).Value
  aIgnoreYears = Intersect(RngToLookIn.EntireRow, rngIgnoreYears.EntireColumn).Value
  EarlierDate = DateAdd("d", -90, DateToCheck)
  For i = 1 To UBound(aRangeToLookIn, 1)
    If Len(aIgnoreLeaver(i, 1)) = 0 And aIgnoreYears(i, 1) <> 0 Then
      For j = 1 To UBound(aRangeToLookIn, 2)
        If aRangeToLookIn(i, j) <= DateToCheck Then
          If aRangeToLookIn(i, j) >= EarlierDate Then
            Count_Rows_With_2Ignores = Count_Rows_With_2Ignores + 1
            Exit For
          End If
        End If
      Next j
    End If
  Next i
End Function

Used like this for example
=Count_Rows_With_2Ignores(F$2:N$11,C15,Q$2:Q$11,D$2:D$11)

Red range is the main range where the supervision dates are recorded
Blue cell is the date that you are checking against
Green range is the column containing any 'Leaver' information
Pink range is the column containing the years of service
 
Upvote 0
Peter_SSs said:
Try the UDF below. For the layout in post 10 with the additional 'Leaver' column in column Q, use it like this

=Count_Rows_With_Ignore(B$3:N$10,D14,$Q$3:$Q$10)

Code: 
Function Count_Rows_With_Ignore(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("d", -90, DateToCheck)
  'EarlierDate = DateAdd("m", -3, DateToCheck)
  For i = 1 To UBound(aRangeToLookIn, 1)
    If IsEmpty(aIgnore(i, 1)) Then
      For j = 1 To UBound(aRangeToLookIn, 2)
        If aRangeToLookIn(i, j) <= DateToCheck Then
          If aRangeToLookIn(i, j) >= EarlierDate Then
            Count_Rows_With_Ignore = Count_Rows_With_Ignore + 1
            Exit For
          End If
        End If
      Next j
    End If
  Next i
End Function
Click to expand...

Hi

Is there a way in the RngToLookIn to select two columns that are not next to each other?

Thank You for your help

J
 
Upvote 0
janarthenan said:
Hi

Is there a way in the RngToLookIn to select two columns that are not next to each other?

Thank You for your help

J
Click to expand...
Could we have some sample data in the layout you are now asking about, and the expected results and explain again in relation to that sample data?
 
Upvote 0
Hi

I am using the following code currently, what i require is, the RngToLookIn are not adjacent, they are several columns apart.
Is there a way to select the range when they are several columns away?

Please see example below code.

Code: 
Function Count_Rows_Supervision(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("d", -90, DateToCheck)
  'EarlierDate = DateAdd("m", -3, 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_Rows_Supervision = Count_Rows_Supervision + 1
            Exit For
          End If
        End If
      Next j
    End If
  Next i
End Function

ABCDEFGHIJK
Staff B
Staff E
Staff H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]STATUTORY AND MANDATORY TRAINING[/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"]Moving People Safely - Practical[/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="align: center"]Leaver[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Infection control - OLD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Moving People Safely - Practical - OLD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: center"]Safeguarding Adults - OLD[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]16/05/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]SN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]21/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]21/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]21/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]18/05/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]27/03/2018[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]09/10/18[/TD]
[TD="align: center"]10/10/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]22/01/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01/01/15[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]03/10/2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]HSCW[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]15/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]01/07/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]15/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]10/01/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff D[/TD]
[TD="align: center"]06/11/2017[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]11/12/17[/TD]
[TD="align: center"]06/11/17[/TD]
[TD="align: center"]1/11/17[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/12/16[/TD]
[TD="align: center"]10/03/16[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]08/04/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]Domestic[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff F[/TD]
[TD="align: center"]05/03/2018[/TD]
[TD="align: center"]HSCW[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"]13/03/18[/TD]
[TD="align: center"]15/08/18[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12/01/17[/TD]
[TD="align: center"]15/04/17[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]23/10/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]HSCW[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]31/10/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]11/12/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]31/10/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]10/09/16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]21/02/2017[/TD]
[TD="align: center"]COOK[/TD]
[TD="align: center"]12/08/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12/08/17[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]12/06/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15/09/17[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Staff I[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]05/03/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]HSCW[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]15/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]17/07/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]15/08/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"][/TD]

</tbody>
Sheet1​




The RangeToLookIn is Column D, with Column I
DateToCheck is Todays Date
rngIgnore is Column G

I am able to select multiple columns when they are next to each other, but when they are several columns apart it doesnt work.

Please advice
J
 
Upvote 0
I'm sorry, I haven't been able to spend time on the forum for a while and that will continue for a while longer.
 
Upvote 0
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.

Code: 
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

ABCDEFGHIJ
Training Stats

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Trainee[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Designation[/TD]
[TD="align: center"]Infection control[/TD]
[TD="align: center"]Infection control - Previous[/TD]
[TD="align: center"]Safeguarding Adults[/TD]
[TD="align: center"]Safeguarding Adults - Previous[/TD]
[TD="align: center"]Basic Life Support[/TD]
[TD="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="align: right"][/TD]
[TD="align: center"]Infection Control[/TD]
[TD="align: center"]Safeguarding Adults[/TD]
[TD="align: center"]Basic Life Support[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Level 1[/TD]
[TD="align: center"]Level 2[/TD]
[TD="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: 1063"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 1055"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D20[/TH]
[TD="align: left"]=Count_Training(Tier1[[Infection control]:[Infection control - Previous]],TODAY(),Tier1[Leaver])[/TD]
[/TR]
[TR]
[TH]E20[/TH]
[TD="align: left"]=Count_Training(Tier1[[Safeguarding Adults]:[Safeguarding Adults - Previous]],TODAY(),Tier1[Leaver])[/TD]
[/TR]
[TR]
[TH]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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Conditionally Format cells based on relationship to a max value
Replies
1
Views
109
Fluff
Fluff
J
  • Solved
Is there a way to create a vlookup that brings in something only one time per order #?
Replies
2
Views
252
jloosen
J
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
2K
KRice
K
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
923
ashleighbrown
A
I
  • Question Question
Tracking vacation requests
Replies
9
Views
991
KRice
K

Forum statistics

Threads
1,224,766
Messages
6,180,846
Members
453,001
Latest member
coulombevin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top