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.

[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

 

[janarthenan]

Peter!

You are a LEGEND!

Thanks - it does exactly what I needed.

Thank You for your patience I really appreciate it!

God Bless
Jan

 

[Peter_SSs]

You are very welcome. Glad it worked for you.

 

[janarthenan]

Hi Peter

Could I be a pain again.

Your code works great, I was just wondering if you will be able to add something to it.

I tried to tweak the code, but I am not that advanced to do something.

I would like the Count_Rows to ignore a row if there is a value in a particular column.

For Example:

[TABLE="width: 750"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Leaver[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]10/3/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5/2/17[/TD]
[TD][/TD]
[TD]5/2/17[/TD]
[TD]Resigned[/TD]
[/TR]
</tbody>[/TABLE]

I need count_rows to ignore Row C when there is a value in the Leaver Column.

Thank You,

Jan

 

[C with no eyes]

Can't think of any way but the long way:

=IF(COUNTIFS($B$2:$N$2,">"&D14-90,$B$2:$N$2,"<="&D14)>0,1,0)+IF(COUNTIFS($B$3:$N$3,">"&D14-90,$B$3:$N$3,"<="&D14)>0,1,0)+IF(COUNTIFS($B$4:$N$4,">"&D14-90,$B$4:$N$4,"<="&D14)>0,1,0)+IF(COUNTIFS($B$5:$N$5,">"&D14-90,$B$5:$N$5,"<="&D14)>0,1,0)+IF(COUNTIFS($B$6:$N$6,">"&D14-90,$B$6:$N$6,"<="&D14)>0,1,0)+IF(COUNTIFS($B$7:$N$7,">"&D14-90,$B$7:$N$7,"<="&D14)>0,1,0)+IF(COUNTIFS($B$8:$N$8,">"&D14-90,$B$8:$N$8,"<="&D14)>0,1,0)+IF(COUNTIFS($B$9:$N$9,">"&D14-90,$B$9:$N$9,"<="&D14)>0,1,0)

Not very versatile as it will not update nicely if a new supervisee is added.

 

[Peter_SSs]

I need count_rows to ignore Row C when there is a value in the Leaver Column.

Jan
Does this relate to the data like in post 5?
Which column is the 'Leaver' column?

 

[janarthenan]

Peter

Yes Its exactly the same data. The Leaver is Column Q.
If Possible Count_Rows to add a rule to ignore if any value is present in the leavers column.

Regards
Jan

 

[Peter_SSs]

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

 

[janarthenan]

Hi Peter

That sortof works.

The problem is that the Leaver column contains a VLOOKUP formula, so I have to physically delete each "empty" value for the equation to work.

Jan

 

[Peter_SSs]

HThe problem is that the Leaver column contains a VLOOKUP formula,..

Try

<del>If IsEmpty(aIgnore(i, 1)) Then</del>
If Len(aIgnore(i, 1)) = 0 Then

 

[janarthenan]

Again Peter

You have delivered!!

I was trying IsBlank - but realised its a WS formula, not suitable for VBA.

Thanks

Jan