COUNTIF/SUMIF gives error if criteria string is longer than 256 characters

Question

While trying to use COUNTIF and SUMIF with a table that regularly has long comments, I kept getting a #VALUE error. A little bit of research said that the error could be due to the criteria string topping the 256 character point.

Any suggestions on how to get around this? I've worked out a solution I'll be posting as an Answer, but I'd like to see if anyone else has a Better Way.

Answer 1

I ended up writing a pair of UDFs in VB to get around the issue. There's still a character limit, but now it's 2^32, rather than 2^8.

The COUNTIF variation was pretty straightforward...

    Function COUNTIFLONG(rng As Range, crt As String, ExactMatch As Boolean)

    Dim Cell As Range
    Dim x As Integer

    x = 0

    For Each Cell In rng
        If IsNull(Cell.Value) Then GoTo CellCont
        If ExactMatch Then
          If Cell.Value = crt Then
            x = x + 1
          End If
          Else
            If (InStr(Cell.Value, crt) > 0) Then
              x = x + 1
            End If
        End If
CellCont:
    Next Cell

    COUNTIFLONG = x

End Function

The SUMIF variation was a bit more tricky to get it to be flexible enough for regular use.

 Function SUMIFLONG(rngCrt As Range, crt As String, rngSum As Range, ExactMatch As Boolean)

    Dim Cell As Range
    Dim x As Integer
    Dim CrtRows As Integer, CrtCols As Integer, SumRows As Integer, SumCols As Integer
    Dim RowOffset As Integer, ColOffset As Integer
    Dim SumDir As String

    CrtRows = rngCrt.Rows.Count
    CrtCols = rngCrt.Columns.Count
    SumRows = rngSum.Rows.Count
    SumCols = rngSum.Columns.Count

    crt = Trim(crt)

    x = 0

    If (CrtRows <> SumRows) Or (CrtCols <> SumCols) Then
        Debug.Print ("Arrays are not the same size.  Please review the formula.")
        Exit Function
    End If

    If (CrtRows <> 1) And (CrtCols <> 1) And (SumRows <> 1) And (SumCols <> 1) Then
        Debug.Print ("Please restrict arrays to one column or row at a time.")
        Exit Function
    End If

    'Detects the offset of the Sum row/column from the Criteria row/column
    RowOffset = rngSum.Row - rngCrt.Row
    ColOffset = rngSum.Column - rngCrt.Column

    For Each Cell In rngCrt
    'Ignores Null cells or rows where the Sum column's value is not a number.
        If IsNull(Cell.Value) Or (Not IsNumeric(Cell.Offset(RowOffset, ColOffset).Value)) Then
          GoTo CellCont
        End If

    'Adds Sum Column's value to the running total.
    'If an Exact Match is not requested, will detect whether Criteria is present in target cell.
        If ExactMatch Then
          If Cell.Value = crt Then
            x = x + Cell.Offset(RowOffset, ColOffset).Value
          End If
          Else
            If (InStr(Cell.Value, crt) > 0) Then
              x = x + Cell.Offset(RowOffset, ColOffset).Value
            End If
        End If
 CellCont:
    Next Cell

    SUMIFLONG = x

 End Function

As I said, I'd like to see if anyone had better Ideas of how to accomplish this, but I hope this helps!