I cannot get the MS recommended 'work-around' for COUNTIF/COUNTIFS criteria greater than 255 characters to produce any result beyond a worksheet #VALUE! error.
Source: COUNTIF function
Wrong value returned The COUNTIF function returns incorrect results when you use it to match strings
for long strings longer than 255 characters.
To match strings longer than 255 characters, use the CONCATENATE function or
the concatenate operator &. For example,
=COUNTIF(A2:A5,"long string"&"another long string")
Sample text string with a 504 character length string in A2:A5,
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur et mi congue, ullamcorper, nulla non, interdum augue. Etiam at turpis sagittis, auctor ante quis, imperdiet neque. Nulla ut mauris ac enim rutrum congue. Vivamus elementum nisi et lectus scelerisque bibendum. Mauris tristique condimentum purus, dictum sagittis velit. Cras non purus ut magna placerat suscipit nec sit amet est. Nam finibus sed nibh ac euismod. Nam fermentum mattis quam, a commodo elit suscipit nec. In eu ipsum iaculis.
Formula per support.office.com recommendations,
'this exceeds 255 character limit
=countif(a:a, a2)
'this is the recommended work-around
=countif(a:a, left(a2, 255)&mid(a2, 256, 255))
'alternate for a2 < 256 characters
=countif(a:a, left(a2, 255)&iferror(mid(a2, 256, 255), text(,)))
Result should be 4. I cannot get anything but #VALUE! if A2 contains any text with a length longer than 255.
Hard-coding the first 255 characters suffixed with the second remaining 249 characters does nothing but return the same #VALUE! error.
=COUNTIF(A:A, "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur et mi congue, ullamcorper, nulla non, interdum augue. Etiam at turpis sagittis, auctor ante quis, imperdiet neque. Nulla ut mauris ac enim rutrum congue. Vivamus elementum nisi et lectus s"&"celerisque bibendum. Mauris tristique condimentum purus, dictum sagittis velit. Cras non purus ut magna placerat suscipit nec sit amet est. Nam finibus sed nibh ac euismod. Nam fermentum mattis quam, a commodo elit suscipit nec. In eu ipsum iaculis.")
I guess you want a question. Does this work at all? What am I missing? Is it my xl2010/xl2016 versions? The documentation I've referred to seems current.
Addendum:
If this does not work as described (or even as imagined) then a proof of a broken concept is easily an acceptable answer.
This will do strings up to 508 character fairly reliably.
=COUNTIFS(D1:D5,LEFT(D1,254)&"*",D1:D5,"*" &RIGHT(D1,254))
It will, in fact, do larger strings by comparing the last 254 and the first 254. Which may or may not suffice for individual requirements.
But may give false positives if the search parameters are shorter than 255 and, the to be searched ends and begins with the same text. For example test test will be counted twice if A1 is test.