2

I'm working on a project that involves translating a few thousand different words from English to German. I've gotten back five different translations, but not all the translations are complete.

I have a Google Sheet with English in column A and the different translations in B, C, D, E and F. Any given row will always have an English word in A, but some cells may be empty as the different translators didn't always recommend a word when they didn't know what to use (some of the terms are highly technical).

I'd like to use a formula to tell me how many translations in each row are present (1, 2, 3, 4 or 5 out of 5).

In the next column, I also want to use a formula to determine how many translations are perfectly identical (1, 2, 3, 4 or 5 out of 5).

Can this be done? If the identical translations formula wouldn't work, can anyone think of a formula I can use to tell me the most popular of the translations?

Improve this question
1
  • A clarification: if five translations are A, A, B, B, C, what do you expect as an answer to "how many are idenfical"? Would it be better to ask "how many distinct translations are there"?
    – user135384
    Commented Mar 19, 2018 at 21:56

1 Answer 1

Reset to default
3

G2:

=5-COUNTBLANK(B2:F2)

H2:

=ARRAYFORMULA(SORTN(TRANSPOSE({B2:F2;COUNTIF(B2:F2,B2:F2)}),1,2,2,0))
  • COUNTIF to count number of occurrences of each distinct entity
  • SORTN to get the most popular one in the descending order of counts.
Improve this answer
1
  • 1
    StackExchange doesn't want me to say something like "thanks," but this is an almost impossibly helpful answer, so thanks!
    – gesher
    Commented Mar 20, 2018 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.