Excel, get arround the limit of CountIf()

Question

so I have an Excel Worksheet with the Mail-Addresses of all resposible persons for the specific items. Some people appear more than once, but when I try =COUNTIF(A2:A9999;A2) and then copy it down, I get the error #VALUE!.

I looked it up, and there is a character limit of 255. I used =LEN(A2) to find out how long the cells were, and they get up to a length of 1500 chars.

Is there still a way to get around that limit, or something else?

Thanks for the help.

Kind regards Elias

Answer 1

You seem to have two problems: you are dragging down the formula F(A2:A9999,A2) (which causes the formula to modify into F(A3:A10000,A3), F(A4:A10001,A4), ... while you are interested in F(A2:A9999,Ax) for every x. As indicated by Solar Mike, you can solve this using F(A$2:A$9999,Ax).

Next you have the problem with the Countif() limitation. There already is the proposal to shorten your string, e.g. by using LEFT(range,225) but I believe the first thing you should be looking for, is the uniqueness of your data: you are saying that your data are e-mail addresses. Are those e-mail addresses of different companies (which would mean that it might be useful to check the RIGHT() part instead of the LEFT() part?

I would advise you to check those really large addresses (1500 characters long? I've never seen such a long address), try to shorten them and use your CountIf() function on those shortened e-mail addresses.