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.

TesseractE · Accepted Answer · 2016-07-01T21:01:42.353

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!

In `COUNTIFLONG` user defined function, I would like to use `select case` statement to test `ExactMatch` rather than `If...then...else` statement. — PaichengWu, Jul 01 '16 at 23:23
Good call, thanks! I'll update that at some point. I need to get in the habit of using CASE more often... — TesseractE, Jul 02 '16 at 02:16

score 2 · Answer 2 · answered Jul 01 '16 at 21:47

2

Without sample data any suggestion is going to involve some guesswork but it sounds like your search criteria could be chopped down to unique pieces less than the 255 character limit and wrapped in wildcards.

=COUNTIF(A:A, "*"&C2&"*")

^{Click for full size image}

answered Jul 01 '16 at 21:47

Yeah, the substrings I had to search for were sometimes too long themselves, actually. I can't provide any sample data, as it's confidential info, but your samples are pretty close to the style of what I was working with. – TesseractE Jul 01 '16 at 21:58

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

2 Answers2