VBA for count sumif result for range (Macro or UDF)

[maabadi]

Hi Guys.
I want to write UDF or macro to count sumif result if more than one criteria.
I write both But have Problems.
1. User Defined Function:

Function CountSumResult(InputRange As Range, Criteria As Variant, SumRange As Range, SumCriteria As Long) As Long
    Dim Cell As Range
    Dim SumResult As Long
    Set 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
             WorksheetFunction.SumIf(SumRange, Criteria) = SumResult
             WorksheetFunction.CountIf(SumResult, SumCriteria) = CountSum
             CountSumResult = CountSum + CountSumResult
           Else
           CountSumResult = CountSumResult
          End If
     Next Cell
End Function

I Know have problems. but I don't see it when write at Insert function.

2. Macro Shows missmatch for Sumresult

Sub Countsssss()
    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 Long
    Dim CountSum As Long
    
    CountSumResult = 0
    Set InputRange = Range("A2:A22")
    Set SumRange = Range("C2:C22")
    SumCriteria = " >= " & 170
    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.SumIf(SumRange, Range("$B$2")) + WorksheetFunction.SumIf(SumRange, Range("$B$3"))
            Debug.Print SumResult
            CountSum = WorksheetFunction.CountIf(SumResult, SumCriteria)
            Debug.Print CountSum
            CountSumResult = CountSum + CountSumResult
            Debug.Print CountSumResult
           Else
            CountSumResult = CountSumResult
          End If
    Next Cell
End Sub

Please help

 

[Fluff]

The Countif function requires a range, not a single value.

 

[maabadi]

Thanks Fluff. I understand and after try and try, I find Solution.
1. Macro

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

2. UDF

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

 

[Fluff]

Glad you sorted it & thanks for the feedback.