Count employees that have more than 100 hours of leave and rdo combined

[asad]

Hello Guys
I am after a formula to count number of employees that have more than 100 hours of leave and rdo combined.
The data is employee name in column A, column B has description - leave, or RDO. Column C has number of hours.
Thanks in advance for your kind help.
Asad

 

[asad]

I write This Macro.
1. You can Change InputRange, CriteriaRange & SumRange Based your data location at worksheet.
2. You can Change Range("$B$2") to your first criteria Location or Name. example: to "Leave"
3. You can Change Range("$B$3") to your Second criteria Location or Name. example: to "RDO"
4. Result Show at Range("E2"). You can change it to what you want.

Sub CountSumResults()
    Dim InputRange As Range
    Dim Criteria As Variant
    Dim SumRange As Range
    Dim CountSumResult As Long
    Dim SumCriteria As Variant
    Dim Cell As Range
    Dim SumResult As Variant
    Dim CountSum As Long
    Dim CriteriaRange As Range
    CountSumResult = 0
    'Set InputRange for Employee Names
     Set InputRange = Range("A2:A22")
     'Set CriteriaRange for Criteria Column ("leave" & "RDO")
    Set CriteriaRange = Range("B2:B22")
    'Set SumRange for Total Hour Column
    Set SumRange = Range("C2:C22")
    'Divide your Criteria Hour to 24 Because one day is 24 Hours
    SumCriteria = 100 / 24
    For Each Cell In InputRange
          If WorksheetFunction.CountIf(Range(Cells(InputRange.Row, Cell.Column).Address, _
          Cells(Cell.Row, Cell.Column).Address), Cell.Value) = 1 Then
          SumResult = WorksheetFunction.Sum(WorksheetFunction.SumIfs(SumRange, CriteriaRange, Range("$B$2"), _
            InputRange, Cells(Cell.Row, Cell.Column).Value), WorksheetFunction.SumIfs(SumRange, CriteriaRange, _
            Range("$B$3"), InputRange, Cells(Cell.Row, Cell.Column).Value))
            If SumResult >= SumCriteria Then
               CountSum = 1
            Else
               CountSum = 0
            End If
               CountSumResult = CountSum + CountSumResult
           Else
               CountSumResult = CountSumResult
          End If
    
    Next Cell

    Range("E2").Value = CountSumResult
End Sub

Thanks maabadi
This is another option and works too
But I will have check with the end user if he/she is happy to use a macro.
If yes, then I will use this.
Thanks a lot again.

 

[maabadi]

Another Option Also is User Defined Function.
You Should Save it at Personal Macro Workbook.
and Use at Function.

Function CountSumResult(InputRange As Range, CriteriaRange As Range, Criteria1 As Variant, Criteria2 As Variant, SumRange As Range, SumCriteria As Long) As Long

    Dim Cell As Range
    Dim SumResult As Long
    Dim CountSum As Long
    CountSumResult = 0
    
    For Each Cell In InputRange
        
          If WorksheetFunction.CountIf(Range(Cells(InputRange.Row, Cell.Column).Address, _
          Cells(Cell.Row, Cell.Column).Address), Cell.Value) = 1 Then
          
            SumResult = WorksheetFunction.SumIfs(SumRange, CriteriaRange, Criteria1, InputRange, _
            Cells(Cell.Row, Cell.Column).Value) + WorksheetFunction.SumIfs(SumRange, CriteriaRange, _
            Criteria2, InputRange, Cells(Cell.Row, Cell.Column).Value)
            Debug.Print SumResult
            'CountSum = WorksheetFunction.CountIf(SumResult, SumCriteria)
            If SumResult >= SumCriteria Then
               CountSum = 1
            Else
               CountSum = 0
            End If
            
            Debug.Print CountSum
            CountSumResult = CountSum + CountSumResult
            Debug.Print CountSumResult
           Else
            CountSumResult = CountSumResult
          End If
     
    Next Cell
  
End Function