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

 

[maabadi]

1. Copy your employees column and paste to other column (for example Column G , started at G3 )
2. With selected Pasted Data, Go to Data tab, at Data tools Section, Select Remove Duplicates.
3. Change your column number format at H to [h]:mm:ss
4.we supposed here your data Located at A2 to C20 . Write at (one column after pasted data) here H3 : = SUMIF($A$2:$A$20,G3,$C$2:$C$20) and drag it down until your last name.
5. At I3 write : = COUNTIF($H$3:$H$20,">(25/6)")


OR
If you know you can use from Pivot tables, it is very easy and fast.

 

[maabadi]

Sorry at I3 write:
= COUNTIF($H$3:$H$20,">="&(25/6)")

 

[asad]

I was trying to avoid helper columns.
And this will also sum for categories other than leave and RDO.
I was thinking of along the lines of sum(if(sum(if(employees=employees,category=leave/rdo,then sum hours))≥100),1)
This will be answer in one cell rather than using helper columns.
Will that work?

 

[asad]

And of course this will be an array formula

 

[maabadi]

With your situation you have repetitive result at the front of each row have same employee.
But Yes I think.

 

[asad]

I will try this tomorrow at office.
Was hoping for someone to help me build this.
I tried today but was unsuccessful.

 

[asad]

I will keep trying
As they say

 

[asad]

I tried tried and got tired........
No luck

Does anyone have another suggestion? Please.

If someone wants to see a sample data, please let me know. I will post a sample of data here.

 

[maabadi]

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