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?

  • 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 Mar 19 '18 at 21:56
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.
  • 1
    StackExchange doesn't want me to say something like "thanks," but this is an almost impossibly helpful answer, so thanks! – gesher Mar 20 '18 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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