0

I just found out that when you're doing a COUNTIF() formula on a cell with more than 255 characters, it retun a #VALUE error.

Do you know how to deal with that?

Thanks in advance.

Verd'O
  • 101
  • 1
  • 10

1 Answers1

0

According to Excel documentation:

The COUNTIF function returns incorrect results when you use it to match 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").

COUNTIF Function

Jacob Edmond
  • 808
  • 2
  • 7
  • 14
  • Thank you for your answer, unfortunately I have to do this to multiple values and it's impossible to use your tip. Thank you for your time. – Verd'O Feb 27 '17 at 14:00
  • What criteria do you need to use for your COUNTIF? – Jacob Edmond Feb 27 '17 at 14:15
  • To replace the COUNTIF function, I can totally use a SUMPRODUCT. But I can't use sumproduct with COUNTIFS. See more here: http://stackoverflow.com/questions/40039282/identify-names-with-same-id-and-same-age – Verd'O Feb 27 '17 at 14:34