In this sheet I'm trying to count different destination. It seems to be problematic when it comes to Æ, Ø and Å.
The formula
=COUNTA(B3:B12) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(TRIM(IF(LEN(B3:B12);IFERROR(REGEXEXTRACT(LOWER(B3:B12);"^([\w\-] )");B3:B12);))))&" differenct countries"
How can I make the formula accept these characters?
CodePudding user response:
the TRIM(IF(LEN(B3:B14);IFERROR(
is unnecessary in your formula.
the formula can be contracted to:
=COUNTA(B3:B14) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(REGEXEXTRACT(LOWER(B3:B14);"\w ")))&" different countries"
EDIT:
if there will be any blanks in the data, then TRIM(IF(LEN(B3:B14);
is unnecessary, and the formula can be contracted to:
=COUNTA(B3:B14) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(LOWER(B3:B14);"\w ");)))&" different countries"