Challenge with countif limitations

[PatrickK777777]

Hello guys. I have a problem here. Am doing some complex data manipulation requiring use of the countif function repeatedly, but I just realized this function breaks down for criteria values longer than 255 characters. I've tried using a custom modifiedcountif function to bypass this limitation but it is really slow. Any better suggestions on this?!

 

[PatrickK777777]

Did you try that method in VBA, or in a straight COUNTIF function right on your worksheet?
If you did not try the later, try it now and see if that makes a difference.

I've tried in both. Am splitting up the item using left/right functions and joining it with either "&" or concatenate. Result is not what is expected

 

[PatrickK777777]

In Fact it still results in the expected error

I've tried in both. Am splitting up the item using left/right functions and joining it with either "&" or concatenate. Result is not what is expected

 

[Joe4]

OK, I don't think I am going to be able to be of any help without being able to see your data and formula attempts.

 

[PatrickK777777]

High guys. Sorry am not authorized to share business data but still waiting for a response to a possible faster workaround of the 255 character length limitation of countif.
BTW I even tried to use =application.evaluate("sumproduct()") coz I tested sumproduct and saw that handles very long strings, but unfortunately the evaluate function cannot. Please note that I want to do this countif calculation via vba.

 

[PatrickK777777]

In case someone in future follows this thread with a similar challenge, note that I was able to gain significant speed with this function.

Function ModifiedCountif(ReferenceRange As Range, Criteria As Variant) As Long

Dim i As Long, k As Long, FirstRow As Long, LastRow As Long, FirstColumn As Long, LastColumn As Long
Dim StopPointRow As Long, StartPointRow As Long
Dim UsedCellsArray() As String, x As Long
Dim ws As Worksheet

If Len(Criteria) <= 255 Then
    ModifiedCountif = Application.WorksheetFunction.CountIf(ReferenceRange, Criteria)
    Exit Function
   
Else
    FirstRow = FirstCellRow(ReferenceRange)
    LastRow = LastCellRow(ReferenceRange)
    FirstColumn = FirstCellColumn(ReferenceRange)
    LastColumn = LastCellColumn(ReferenceRange)
   
    'Initialise
    Set ws = ReferenceRange.Worksheet
    ModifiedCountif = 0
   
    For i = FirstColumn To LastColumn
        If Not IsEmpty(ws.Cells(LastRow, i)) Then
            StopPointRow = LastRow
            If Not IsEmpty(ws.Cells(LastRow - 1, i)) Then
                StartPointRow = ws.Cells(LastRow, i).End(xlUp).Row
                If StartPointRow < FirstRow Then
                    StartPointRow = FirstRow
                End If
            Else
                StartPointRow = LastRow
            End If
        Else
            StopPointRow = ws.Cells(LastRow, i).End(xlUp).Row
            If StopPointRow < FirstRow Then
                GoTo LEAVECURRENTCOLUMN
            End If
            If StopPointRow > 1 Then
                If Not IsEmpty(ws.Cells(StopPointRow - 1, i)) Then
                    StartPointRow = ws.Cells(StopPointRow, i).End(xlUp).Row
                    If StartPointRow < FirstRow Then
                        StartPointRow = FirstRow
                    End If
                Else
                    StartPointRow = StopPointRow
                End If
            Else
                If Not IsEmpty(ws.Cells(StopPointRow, i)) Then
                    StartPointRow = StopPointRow
                Else
                    GoTo LEAVECURRENTCOLUMN
                End If
            End If
        End If
       
COUNTINGSTEP:
        'Count
        For k = StopPointRow To StartPointRow Step -1
            If ws.Cells(k, i).Value = Criteria Then
                ModifiedCountif = ModifiedCountif + 1
            End If
        Next k
       
        If StartPointRow > FirstRow Then
            StopPointRow = ws.Cells(StartPointRow, i).End(xlUp).Row
            If StopPointRow < FirstRow Then
                GoTo LEAVECURRENTCOLUMN
            End If
            If StopPointRow > 1 Then
                If Not IsEmpty(ws.Cells(StopPointRow - 1, i)) Then
                    StartPointRow = ws.Cells(StopPointRow, i).End(xlUp).Row
                    If StartPointRow < FirstRow Then
                        StartPointRow = FirstRow
                    End If
                Else
                    StartPointRow = StopPointRow
                End If
            Else
                If Not IsEmpty(ws.Cells(StopPointRow, i)) Then
                    StartPointRow = StopPointRow
                Else
                    GoTo LEAVECURRENTCOLUMN
                End If
            End If
        Else
            GoTo LEAVECURRENTCOLUMN
        End If
       
        If StartPointRow >= FirstRow Then
            GoTo COUNTINGSTEP
        End If
       
LEAVECURRENTCOLUMN:
    Next i
End If
   
End Function

where the other 4 custom functions used are:

Function FirstCellRow(RangeToCheck As Range) As Long

For Each Cell In RangeToCheck
    FirstCellRow = Cell.Row
    Exit Function
Next Cell

End Function

and

Function FirstCellColumn(RangeToCheck As Range) As Long

For Each Cell In RangeToCheck
    FirstCellColumn = Cell.Column
    Exit Function
Next Cell

End Function

and

Function LastCellRow(RangeToCheck As Range) As Long
LastCellRow = FirstCellRow(RangeToCheck) + RangeToCheck.Rows.count - 1
End Function

Thanks @Joe4 for trying to help me but sharing the data could pose serious risks for me.

Function LastCellColumn(RangeToCheck As Range) As Long
LastCellColumn = FirstCellColumn(RangeToCheck) + RangeToCheck.Columns.count - 1
End Function