Countif only one per row

[janarthenan]

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.

Name	April	May	June	July	August	Sept	Oct	Nov	Dec	Last Supervision	Next Supervision
A	1/4/17		1/6/17	1/7/17						=MAX(B2:J2)	=H2+90
B		1/5/17			1/8/17					=MAX(B3:J3)	=H3+90
C										..	..
D
E
F

<tbody>
</tbody>

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

 

[janarthenan]

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

 

[Peter_SSs]

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

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?

 

[janarthenan]

Hi Peter

This was the code you originally created for me:

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.

	B	C	D	E	F	G	H	I
2	1	05/06/2017	0	HSCW			06/06/2017
3	2	03/10/2016	1	HSCW
4	3	04/08/2017	0	HSCW
5	4	02/05/2017	0	HSCW
6	5	01/08/2014	3	SHSC	01/04/2017			26/07/2017
7	6	06/11/2017	0	HSCW
8	7	14/08/2017	0	HSCW
9	8	23/10/2017	0	HSCW
10	9	21/02/2017	1	COOK	07/04/2017
11	10	14/11/2016	1	HSCW		05/05/2017

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

 

[Peter_SSs]

OK, try this

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

 

[janarthenan]

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)

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

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

 

[Peter_SSs]

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

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?

 

[janarthenan]

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.

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

	A	B	C	D	E	F	G	H	I	J	K
1	STATUTORY AND MANDATORY TRAINING	Start Date	Designation	Infection control	Moving People Safely - Practical	Safeguarding Adults	Leaver		Infection control - OLD	Moving People Safely - Practical - OLD	Safeguarding Adults - OLD
2	Staff A	16/05/2018	SN	21/08/18	21/08/18	21/08/18			18/05/17
3	Staff B	27/03/2018	HSCW	09/10/18	10/10/18	15/08/18			22/01/17		01/01/15
4	Staff C	03/10/2016	HSCW	15/08/18	01/07/18	15/08/18			10/01/17
5	Staff D	06/11/2017	HSCW	15/08/18	11/12/17	06/11/17	1/11/17			11/12/16	10/03/16
6	Staff E	08/04/2014	Domestic
7	Staff F	05/03/2018	HSCW	15/08/18	13/03/18	15/08/18				12/01/17	15/04/17
8	Staff G	23/10/2017	HSCW	31/10/18	11/12/17	31/10/18				10/09/16
9	Staff H	21/02/2017	COOK	12/08/17		12/08/17			12/06/16		15/09/17
10	Staff I	05/03/2018	HSCW	15/08/18	17/07/18	15/08/18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

 

[Peter_SSs]

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.

 

[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

<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]) <tbody> </tbody>

<tbody>
</tbody>