Counting single instances in a column

[wahur]

Got a table of projects few thousands of rows. There is a column for target language (EN, DE, ES etc.) and there is a column with translator name.

How do I get number of translators who work into one specific language?

Wahur

[Sheila D]

Have a look at Dcount or COuntif - one or the other should do what you want.
HTH

Sheila
www.c-i-m-s.com
MS Office training, London

"wahur" wrote:

>
> Got a table of projects few thousands of rows. There is a column for
> target language (EN, DE, ES etc.) and there is a column with translator
> name.
>
> How do I get number of translators who work into one specific
> language?
>
> Wahur
>
>
> --
> wahur
> ------------------------------------------------------------------------
> wahur's Profile: http://www.excelforum.com/member.php...o&userid=26555
> View this thread: http://www.excelforum.com/showthread...hreadid=559269
>
>

[wahur]

[QUOTE=Sheila D]Have a look at Dcount or COuntif - one or the other should do what you want.
HTH

DCOUNT does not work - it only counts cells with numbers - I have text
DCOUNTA does count instances of language - telling me I have x projects done into that language
COUNTIF does the same as DCOUNTA

unless you can provide some clever trick that I do not know.

Maybe I should clarify. The table has 1000s of rows(projects), hundreds of translators, many of whom do more than on project and also many that do different languages. I need an answer to the question: how many translators do I have for language X?

And please don't accuse me about forcing Excel doing database work. I know that already. Not my fault.

Vahur

[Domenic]

Can you provide a sample of your data, along with expected results?

In article <[email protected]>,
wahur <[email protected]> wrote:

> DCOUNT does not work - it only counts cells with numbers - I have text
> DCOUNTA does count instances of language - telling me I have x projects
> done into that language
> COUNTIF does the same as COUNTA
>
> unless you can provide some clever trick that I do not know.
>
> Vahur

[wahur]

EN John
EN Jack
EN John
EN Anne
FR Peter
FR John

I want to get that I've got 3 translators for EN and 2 translators for FR
It does not have to be one formula, e.g I can autofilter by language or sort them by lang and copy by lang to other sheets. To use IT terminology, I need to find out how many unique strings are in a column.

Vahur

[Domenic]

Assuming that A2:B7 contains the data, let D2 contain EN, and D3 contain
FR, then try the following...

[Option 1]

Download and and install the free add-in Morefunc.xll, then try the
following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

E2, copied down:

=COUNTDIFF(IF(A$2:A$7=D2,IF(B$2:B$7<>"",B$2:B$7)),,FALSE)

[Option 2]

E2, copied down:

=COUNT(1/FREQUENCY(IF(A$2:A$7=D2,IF(B$2:B$7<>"",MATCH(B$2:B$7,B$2:B$7,0))
),ROW(B$2:B$7)-ROW(B$2)+1))

....which also needs to be confirmed with CONTROL+SHIFT+ENTER. Note that
Option 1 is much more efficient.

Hope this helps!

In article <[email protected]>,
wahur <[email protected]> wrote:

> EN John
> EN Jack
> EN John
> EN Anne
> FR Peter
> FR John
>
> I want to get that I've got 3 translators for EN and 2 translators for
> FR
> It does not have to be one formula, e.g I can autofilter by language or
> sort them by lang and copy by lang to other sheets. To use IT
> terminology, I need to find out how many unique strings are in a
> column.
>
> Vahur