# Count the number of nonempty words and find how many are identical

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, 2018 at 21:56

``````=5-COUNTBLANK(B2:F2)
``````=ARRAYFORMULA(SORTN(TRANSPOSE({B2:F2;COUNTIF(B2:F2,B2:F2)}),1,2,2,0))